TSQL中如何实现从表读取列值替换字符串中的标签？

需求场景 #

需要实现一个逻辑：通过TaskID从Tasks表中读取对应列的值，替换字符串中的<标签>占位符。示例调用如下：

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

当TaskID=T001的记录中FirstName列值为Peter时，应返回结果：

The person name is Peter

自定义函数代码 #

用户编写的标量函数如下：

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

报错信息 #

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

解决思路 #

TSQL标量函数不允许执行sp_executesql这类存储过程，以下是三种可行的替代方案：

1. 改用存储过程实现逻辑 #

存储过程支持执行动态SQL，将逻辑迁移到存储过程，通过输出参数返回结果：

CREATE PROCEDURE ReplaceTagsProc
    @TaskID nvarchar(10),
    @TextWithTags nvarchar(max),
    @Result nvarchar(max) OUTPUT
AS
BEGIN
    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 @ColValue = ' + @ColumnName + ' FROM Tasks WHERE TaskID = @TaskID';
    DECLARE @ColValue NVARCHAR(MAX)

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

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

调用方式：

DECLARE @TaskID nvarchar(10) = 'T001';
DECLARE @MyString nvarchar(100) = 'The person name is <FirstName>';
DECLARE @Result nvarchar(max)
EXEC ReplaceTagsProc @TaskID, @MyString, @Result OUTPUT
SELECT @Result

2. 静态列映射（适合列固定的场景） #

如果Tasks表的列是固定的，可通过CASE语句直接匹配标签读取列值，避免动态SQL：

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);

    -- 静态匹配列名读取值
    SELECT 
        @Result = CASE @Tag
            WHEN 'FirstName' THEN FirstName
            WHEN 'LastName' THEN LastName
            -- 可继续添加其他列的映射
            ELSE @TextWithTags
        END
    FROM Tasks WHERE TaskID = @TaskID

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

    RETURN @TextWithTags
END

该方法无SQL注入风险，但新增列时需要手动修改函数。

3. 表值函数处理多标签场景（进阶方案） #

如果字符串包含多个标签，可通过表值函数拆分所有标签，关联Tasks表批量获取列值后拼接回原字符串。核心逻辑是用XML拆分标签列表，再通过动态SQL或静态映射匹配列值，最后循环替换所有标签。

内容的提问来源于stack exchange，提问作者Cameron Castillo