SQL Server : to column merge from a custom table-defined list

Question

Using SQL Server 2012 Express.

I'm building a communications system which emails end users some notifications about upcoming work. I have a table that stores merge field placeholders and the database field which should populate the data into these placeholders.

I've tried to write a function where I can supply an input string with these placeholders, a unique identifier for that user/machine, and the tenantID, and have it return the string with all the placeholder values replaced with the user's/machine's database values.

I'm running into the error:

Only functions and some extended stored procedures can be executed from within a function.

which I've googled and understand why, however I don't know how else I can achieve what I'm trying to do without doing it the way I'm doing it.

The code I've written is as follows:

CREATE TABLE [dbo].[CommsFields]
(
    [ID] [BIGINT] IDENTITY(1,1) NOT NULL,
    [TenantID] [BIGINT] NOT NULL,
    [Placeholder] [VARCHAR](255) NOT NULL,
    [DBField] [VARCHAR](255) NOT NULL
)

INSERT INTO CommsFields (TenantID, Placeholder, DBField) 
VALUES (1, '@@Email', 'u_PrimarySMTP')

CREATE TABLE [dbo].[AllMUs]
(
    [MUID] BIGINT,
    u_PrimarySMTP VARCHAR(255)
)

INSERT INTO AllMUs (MUID, u_PrimarySMTP) 
VALUES (28228, '[email protected]')

CREATE FUNCTION DoFieldMerge
    (@SourceString NVARCHAR(MAX),
     @MUID BIGINT,
     @TenantID BIGINT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @ResultString NVARCHAR(MAX) 
    DECLARE @sql NVARCHAR(MAX)
    DECLARE @PHID AS INT

    DECLARE @PHCursor AS CURSOR

    SET @sql = CONCAT('DECLARE @Tenant bigint = ', @TenantID, '; DECLARE @MU bigint = ', @MUID, '; DECLARE @ResultString2 nvarchar(MAX) = ''', @SourceString, ''';')

    SET @PHCursor = CURSOR FOR
        SELECT ID FROM CommsFields

    OPEN @PHCursor;
    FETCH NEXT FROM @PHCursor INTO @PHID

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = CONCAT(@sql, 'SET @ResultString2 = replace(@ResultString2, ''', (SELECT Placeholder FROM CommsFields WHERE ID = @PHID AND TenantID = @TenantID), ''', (SELECT ISNULL(', (SELECT DBField FROM CommsFields WHERE ID = @PHID AND TenantID = @TenantID), ', '''') FROM AllMUs WHERE MUID = ', @MUID, ' AND TenantID = ', @TenantID, '));')

        FETCH NEXT FROM @PHCursor INTO @PHID
    END

    CLOSE @PHCursor;
    DEALLOCATE @PHCursor;

    --SET @ResultString = @sql
    EXEC sp_executesql @sql, N'@ResultString2 nvarchar(MAX) output', @ResultString output;

    RETURN @ResultString
END
GO

SELECT dbo.DoFieldMerge('test @@Email', 28228, 1)

I expect the output to be 'test [email protected]' but instead I get the error

Only functions and some extended stored procedures can be executed from within a function

If I comment out the 'exec' command near the end, and un-comment the 'SET' command above it, I get the SQL output which I was trying to achieve, and if I run this I get a correct result, but I need to run the contents of @sql within the function and return just the resulting @ResultString.

I've also tried searching for a code snippet that acts as a custom column merger in SQL, but I'm getting so many results which are nothing to do with what I'm really wanting.

Answer 1

The answer here was to instead use a stored procedure with an output parameter. My final SQL ended up as follow:

CREATE PROCEDURE [dbo].[DoMerge]
    @SourceString nvarchar(MAX)
    , @MUID bigint
    , @TenantID bigint
    , @ResultString nvarchar(MAX) output
AS
BEGIN
    SET NOCOUNT ON;



    DECLARE @sql nvarchar(MAX)
    DECLARE @PHID as INT
    DECLARE @PHCursor as CURSOR

    SET @sql = CONCAT('DECLARE @Tenant bigint = ', @TenantID, '; DECLARE @MU bigint = ', @MUID, '; DECLARE @ResultString2 nvarchar(MAX) = ''', @SourceString, ''';')

    SET @PHCursor = CURSOR FOR
        SELECT ID FROM CommsFields

    OPEN @PHCursor;
    FETCH NEXT FROM @PHCursor INTO @PHID

    WHILE @@FETCH_STATUS = 0

    BEGIN

        SET @sql = CONCAT(@sql, 'SET @ResultString2 = replace(@ResultString2, ''', (SELECT CONCAT((SELECT TemplateFieldMarker FROM Config), Placeholder) FROM CommsFields WHERE ID = @PHID AND TenantID = @TenantID), ''', (SELECT ISNULL(', (SELECT DBField FROM CommsFields WHERE ID = @PHID AND TenantID = @TenantID), ', '''') FROM AllMUs WHERE MUID = ', @MUID, ' AND TenantID = ', @TenantID, '));')

        FETCH NEXT FROM @PHCursor INTO @PHID

    END

    CLOSE @PHCursor;
    DEALLOCATE @PHCursor;

    SET @sql = CONCAT(@sql, ' SELECT @ResultString2')

    exec (@sql)

END