存储过程中如何通过动态SQL为@AllCode变量赋值并拼接字符串

现有一个存储过程，接收源表和目标表名作为Varchar类型参数，需要利用源表的Column1字段值，提取每个值的前两位并拼接成类似'09,89,68'的格式，结果存入@AllCode变量用于后续计算。

当直接指定表名时，以下代码可正常工作：

declare @AllCode varchar(max);
select @AllCode = @AllCode + ', ' + Code 
from 
    (select distinct left(Column1,2) as Code 
     from TableA 
     where TableA.Processedflag is null) tmp

print @AllCode --> 09,89,68

但将表名替换为输入参数时，需要使用动态SQL，无法直接完成@AllCode的赋值。当前编写的存储过程如下：

alter procedure temp.spSample
    @SrcTbl varchar(100),
    @TgtTbl varchar(100)
as
begin
    declare @AllCode varchar(500);
    declare @SqlQuery varchar(max);

    set @SqlQuery = 'select distinct left(Column1,2) as Code from ' + @SrcTbl + ' where ' + @SrcTbl  +'.ProcessedFlag is null';

    exec(@SqlQuery);
end

需求：不使用外部创建的表变量（内部表变量存在“表未找到”问题），实现@AllCode的赋值，得到拼接后的结果。

方法1：使用sp_executesql带输出参数 #

通过sp_executesql将动态SQL的结果赋值给外部变量，在动态SQL中用for xml path完成高效字符串拼接，再通过输出参数返回结果：

alter procedure temp.spSample
    @SrcTbl varchar(100),
    @TgtTbl varchar(100)
as
begin
    declare @AllCode varchar(500);
    declare @SqlQuery nvarchar(max); -- sp_executesql要求参数为nvarchar类型

    -- 构建动态SQL，用QUOTENAME避免SQL注入并处理特殊表名
    set @SqlQuery = N'
        select @Result = stuff((
            select '', '' + distinct left(Column1,2)
            from ' + QUOTENAME(@SrcTbl) + N'
            where ProcessedFlag is null
            for xml path(''''), type
        ).value(''.'', ''varchar(max)''), 1, 2, '''')
    ';

    -- 执行动态SQL，将结果赋值给外部变量@AllCode
    exec sp_executesql 
        @SqlQuery,
        N'@Result varchar(500) output',
        @Result = @AllCode output;

    -- 后续可直接使用@AllCode进行计算
    print @AllCode;
end

说明 #

• QUOTENAME(@SrcTbl)用于处理表名包含特殊字符、关键字的情况，同时避免SQL注入风险。
• for xml path是SQL Server中高效的字符串拼接方式，替代循环赋值的低效率写法。
• sp_executesql支持输出参数，可将动态SQL内部的计算结果传递到存储过程的外部变量中。

方法2：动态SQL内部循环赋值（兼容旧版本） #

如果需要兼容不支持for xml path的SQL Server版本，可在动态SQL内部完成循环拼接，再通过输出参数返回：

alter procedure temp.spSample
    @SrcTbl varchar(100),
    @TgtTbl varchar(100)
as
begin
    declare @AllCode varchar(500);
    declare @SqlQuery nvarchar(max);

    set @SqlQuery = N'
        declare @TempCode varchar(500);
        set @TempCode = '''';
        select @TempCode = @TempCode + '', '' + left(Column1,2)
        from (
            select distinct left(Column1,2) as Code
            from ' + QUOTENAME(@SrcTbl) + N'
            where ProcessedFlag is null
        ) tmp;
        set @Result = stuff(@TempCode, 1, 2, '''');
    ';

    exec sp_executesql 
        @SqlQuery,
        N'@Result varchar(500) output',
        @Result = @AllCode output;

    print @AllCode;
end

注意事项 #

• 动态SQL拼接表名时必须使用QUOTENAME，避免SQL注入风险。
• 确保@AllCode的长度足够容纳拼接后的结果，防止字符串截断。
• 两种方法均无需外部表变量，完全在存储过程内部实现逻辑。

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