SQL函数替换SET ROW COUNT实现字符串模板参数填充问题

需求背景 #

需要创建可用于SELECT语句的SQL函数，实现：

• 接收带%1、%2等占位符的消息模板（例如：User %2 logged in to %3 at %1）
• 接收无单根节点的XML格式参数（例如：<Param>11:00:00</Param><Param>admin</Param><Param>computer 1</Param>）
• 将参数按序号替换到模板对应位置，生成目标字符串，支持任意数量、顺序、重复的参数，且参数内XML字符已转义

现有问题 #

原独立SQL脚本可正常实现功能，但改写为函数时，用表变量替代临时表后，因SQL函数内禁止使用SET ROW COUNT语句，无法遍历表变量中的参数，导致函数报错。

当前简化后的函数代码 #

CREATE FUNCTION sch1.getEvent (@eventString nvarchar(max), @eventParams xml)
RETURNS nvarchar(max)
AS 

BEGIN
    
    DECLARE @eventDescription nvarchar(max) 
    SET @eventDescription=@eventString
    
    DECLARE @idx INT
    SET @idx = 0

    DECLARE @tempTable table (
        tempKey int, 
        param nvarchar(max)
    )

    set rowcount 0
    insert @tempTable
    select 
        NULL, 
        s.c.value('.', 'nvarchar(max)')
    from @eventParams.nodes('/Param') as s(c)

    set rowcount 1
    update @tempTable set tempKey = 1

    while @@rowcount > 0
    begin
        DECLARE @currParam nvarchar(max)
        set @idx = @idx+1

        set rowcount 0

        select @currParam = param from @tempTable where tempKey = 1
        set @eventDescription = replace(@eventDescription, '%'+CAST(@idx as varchar), @currParam)

        delete @tempTable where tempKey = 1
        set rowcount 1
        update @tempTable set tempKey = 1
    end

    set rowcount 0


    RETURN @eventDescription
END

原独立SQL代码 #

DECLARE @params xml = '<Param>999</Param><Param>22</Param>'
DECLARE @pattern nvarchar(max) = '%1 :: %2'
DECLARE @paramIndex int = 0

set rowcount 0
select NULL tempKey, s.c.value('.', 'nvarchar(100)') as param into #temp_table3 from @params.nodes('/Param') as s(c)

set rowcount 1
update #temp_table3 set tempKey = 1


while @@rowcount > 0
begin
    DECLARE @currParam nvarchar(max)
    set @paramIndex = @paramIndex+1

    set rowcount 0

    select @currParam = param from #temp_table3 where tempKey = 1
    set @pattern = replace(@pattern, '%'+CAST(@paramIndex as varchar), @currParam)

    delete #temp_table3 where tempKey = 1
    set rowcount 1
    update #temp_table3 set tempKey = 1
end

set rowcount 0

select @pattern

解决方案 #

通过给XML参数生成带顺序序号的数据集，避免使用SET ROW COUNT和表变量的遍历逻辑，直接按序号逐个替换占位符。修改后的函数如下：

CREATE FUNCTION sch1.getEvent (@eventString nvarchar(max), @eventParams xml)
RETURNS nvarchar(max)
AS 
BEGIN
    DECLARE @eventDescription nvarchar(max) = @eventString;
    DECLARE @paramCount INT;
    DECLARE @currentIndex INT = 1;

    -- 生成带顺序序号的参数表
    DECLARE @params TABLE (
        Seq INT PRIMARY KEY,
        ParamValue nvarchar(max)
    );

    INSERT INTO @params (Seq, ParamValue)
    SELECT 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Seq,
        s.c.value('.', 'nvarchar(max)') AS ParamValue
    FROM @eventParams.nodes('/Param') AS s(c);

    SELECT @paramCount = COUNT(*) FROM @params;

    -- 按序号循环替换每个占位符
    WHILE @currentIndex <= @paramCount
    BEGIN
        DECLARE @currParam nvarchar(max);
        SELECT @currParam = ParamValue FROM @params WHERE Seq = @currentIndex;
        
        SET @eventDescription = REPLACE(@eventDescription, '%' + CAST(@currentIndex AS varchar(10)), @currParam);
        SET @currentIndex = @currentIndex + 1;
    END

    RETURN @eventDescription;
END

方案说明 #

1. 使用ROW_NUMBER()为每个XML节点生成唯一顺序序号，确保参数顺序与XML中节点的出现顺序一致
2. 通过参数总数控制循环次数，完全规避SET ROW COUNT的使用，符合SQL函数的语法限制
3. 直接按序号匹配占位符%N进行替换，逻辑更直观，可读性更强

测试验证 #

执行以下语句验证函数功能：

SELECT sch1.getEvent(
    'User %2 logged in to %3 at %1',
    '<Param>11:00:00</Param><Param>admin</Param><Param>computer 1</Param>'
) AS Result;

返回结果：User admin logged in to computer 1 at 11:00:00

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