Replace tags in a string by reading dynamic columns values from a table

Question

Using TSQL, I want to replace tags in a string by reading values from a column in a table.
E.g. I want to call:

DECLARE @TaskID nvarchar(10) = 'T001';
DECLARE @MyString nvarchar(100) = 'The person name is <FirstName>';
SELECT @Result = dbo.ReplaceTags(@TaskID, @TextWithTags)

It should then return:

The person name is Peter

Provided that Peter is stored in col FirstName where TaskID = T001.

This is what I have:

CREATE FUNCTION ReplaceTags
(
    @TaskID nvarchar(10),
    @TextWithTags nvarchar(max)
)
RETURNS nvarchar(max) AS
BEGIN
    DECLARE @Result nvarchar(max)
    DECLARE @Tag nvarchar(30)

    DECLARE @TagStart INT;
    DECLARE @TagEnd INT;
    SET @TagStart = CHARINDEX('<', @TextWithTags) + 1;
    SET @TagEnd = CHARINDEX('>', @TextWithTags, @TagStart);
    SET @Tag = SUBSTRING(@TextWithTags, @TagStart, @TagEnd - @TagStart);

    DECLARE @ColumnName NVARCHAR(100) = QUOTENAME(@Tag); 
    DECLARE @SQL NVARCHAR(MAX) = N'SELECT @Result = ' + @ColumnName + ' FROM Tasks WHERE TaskID = @TaskID';

    EXEC sp_executesql @SQL, N'@TaskID NVARCHAR(10), @Result NVARCHAR(MAX) OUTPUT', @TaskID, @Result OUTPUT;

    SET @TextWithTags = REPLACE(@TextWithTags, '<' + @Tag + '>', @Result)

    RETURN @TextWithTags
END

When testing, I get:

Error is: Msg 557, Level 16, State 2, Line 14 Only functions and some extended stored procedures can be executed from within a function.

I understand the error, but I do not know how to fix. Any ideas?

Answer 1

You can do this if you unpivot the tasks table. Assuming its not too big performance might be OK. There is certainly a cleaner better performing way to write this but this does work.

CREATE FUNCTION ReplaceTags
(
    @TaskID nvarchar(10),
    @TextWithTags nvarchar(max)
)
RETURNS nvarchar(max) 
AS
BEGIN 
    DECLARE @TagStart INT;
    DECLARE @TagEnd INT;
    DECLARE @Tag nvarchar(30);
    SET @TagStart = CHARINDEX('<', @TextWithTags) + 1;
    SET @TagEnd = CHARINDEX('>', @TextWithTags, @TagStart);
    SET @Tag = SUBSTRING(@TextWithTags, @TagStart, @TagEnd - @TagStart);

    DECLARE @Result nvarchar(100)
    SELECT TOP 1
        @Result = REPLACE(@TextWithTags, '<' + @Tag + '>', t.PropertyValue)
        FROM 
        (
        SELECT 
            TaskID 
            , u.PropertyName
            , u.PropertyValue
            FROM Tasks  
            UNPIVOT (PropertyValue FOR PropertyName IN ([FirstName], [LastName]) ) as u 
        ) t
        WHERE t.TaskID = @TaskID and t.PropertyName = @Tag

    RETURN @Result
END