SQL Server函数多表替换异常：仅最后一次生效，存储过程正常

问题根源 #

标量函数中直接使用SELECT @source = REPLACE(@source, longer, shorter) FROM tmp.vaccine_replacements ORDER BY id的写法，并不会像存储过程那样逐行累积替换。实际上，每一行的REPLACE都是基于原始的@source值执行，最终只有最后一行的替换结果会保留，前面的替换操作都被覆盖了。而存储过程的执行逻辑会逐行更新变量，因此能得到正确的累积替换效果。

解决方案 #

方案1：游标逐行处理 #

通过游标遍历替换规则表，逐次累积执行替换操作：

CREATE FUNCTION tmp.fix_template_list_data_test 
    (@source varchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @longer varchar(max), @shorter varchar(max)
    
    DECLARE replace_cursor CURSOR FOR
        SELECT longer, shorter
        FROM tmp.vaccine_replacements
        ORDER BY id

    OPEN replace_cursor
    FETCH NEXT FROM replace_cursor INTO @longer, @shorter

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @source = REPLACE(@source, @longer, @shorter)
        FETCH NEXT FROM replace_cursor INTO @longer, @shorter
    END

    CLOSE replace_cursor
    DEALLOCATE replace_cursor

    RETURN @source
END

方案2：递归CTE无游标实现（SQL Server 2017+） #

利用递归CTE按顺序累积替换结果，避免使用游标：

CREATE FUNCTION tmp.fix_template_list_data_test 
    (@source varchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @result varchar(max) = @source

    ;WITH ReplaceCTE AS (
        SELECT 
            id, 
            longer, 
            shorter,
            REPLACE(@source, longer, shorter) AS replaced_value
        FROM tmp.vaccine_replacements
        WHERE id = (SELECT MIN(id) FROM tmp.vaccine_replacements)

        UNION ALL

        SELECT 
            r.id, 
            r.longer, 
            r.shorter,
            REPLACE(cte.replaced_value, r.longer, r.shorter) AS replaced_value
        FROM tmp.vaccine_replacements r
        JOIN ReplaceCTE cte ON r.id = cte.id + 1
    )
    SELECT @result = replaced_value
    FROM ReplaceCTE
    WHERE id = (SELECT MAX(id) FROM tmp.vaccine_replacements)

    RETURN @result
END

额外注意事项 #

函数输入参数为varchar(max)，返回值为nvarchar(max)，会存在隐式类型转换。建议将输入参数改为nvarchar(max)，保持类型一致，避免潜在的编码问题。

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