SQL Server存储过程拼接参数生成动态表名报未声明表变量错误

SQL Server Management Studio 2018环境下，自定义存储过程需传入2个参数，其中托运单号入参用于拼接生成动态表名，规则如下：

• 入参：@ConsignmentNo
• 字典表名变量：@TmpDictionary = 'tmp.' + @ConsignmentNo + '_Dictionary'
• 词表名变量：@TmpThesaurus = 'mail.' + @ConsignmentNo + '_Thesaurus'

拼接完成的表名格式示例：

tmp.Cons1234_Dictionary

执行存储过程时抛出错误：

Must declare the table variable "@TmpDictionary"（必须声明表变量"@TmpDictionary"）

原存储过程代码如下：

/****** Object:  ---------------   Script Date: 29/06/2022 16:17:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE InsertProcedureNameHere 
    @Source varchar (max), 
    @ConsignmentNo varchar(max)
AS
    DECLARE
        @sql varchar(MAX),
        @loop int,
        @max_loop int,
        @TmpDictionary nvarchar(max) = 'tmp.' + @ConsignmentNo + '_Dictionary',
        @TmpThesaurus nvarchar(max) = 'mail.' + @ConsignmentNo + '_Thesaurus'
        
--set @TmpLookup = 'tmp.' + @JobNumber + '_Mailing_Lookup'
--set @MailSelection = 'mail. + @JobNumber + '_Mailing_Selection'

    SELECT @loop = MIN(ID) FROM @TmpDictionary 
    WHERE [Source] = @Source

    SELECT @max_loop = MAX(ID) FROM @TmpDictionary 
    WHERE [Source] = @Source

    --print @loop print @max_loop
    WHILE @loop <= @max_loop 
    BEGIN
        SELECT 
                      ---------                                                          
        FROM @TmpDictionary t
        WHERE ID = @loop
               
        BEGIN
            SET @sql = '
                                update t
                                -------------------
                                from @Thesaurus t
                                where 1=1
                                -------------------                          
            PRINT (@sql)
            -- EXEC (@sql)
        END
                        
        SET @loop = @loop +1
    END

代码中横线为业务逻辑占位符，不影响问题定位。核心需求为：存储过程接收入参后拼接生成动态表名，配合动态SQL完成动态表的引用操作。

核心问题是将存储动态表名的字符串类型变量，直接作为表对象在静态SQL语句中引用：

1. SQL Server编译存储过程时，会逐行校验所有数据操作语句引用的对象合法性。代码中写FROM @TmpDictionary时，数据库会将@TmpDictionary识别为表类型变量，但实际声明的@TmpDictionary是nvarchar(max)类型的字符串，并非表变量，编译阶段直接抛出“必须声明表变量”的错误。
2. 写在@sql字符串内的@Thesaurus存在执行上下文问题：动态SQL运行在独立执行上下文，外层声明的普通变量无法直接在动态SQL字符串内识别，且表名属于数据库标识符，无法通过变量传参的方式直接引用。
3. 直接拼接用户传入的参数生成表名存在SQL注入风险，必须做入参合法性校验。

1. 新增入参校验逻辑，过滤托运单号中的特殊字符，避免SQL注入。
2. 存储表名的变量使用SQL Server专用的标识符类型sysname，通过QUOTENAME()包裹拼接后的表名，避免特殊字符导致语法错误。
3. 所有引用动态表的查询逻辑，全部通过sp_executesql执行拼接完成的动态SQL，需要在动态SQL和外层存储过程间传递值时，使用参数绑定实现。

修正后的代码示例：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE InsertProcedureNameHere 
    @Source varchar (max), 
    @ConsignmentNo varchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    -- 入参校验：仅允许托运单号包含字母、数字
    IF @ConsignmentNo LIKE '%[^a-zA-Z0-9]%'
    BEGIN
        RAISERROR('托运单号包含非法字符，仅支持字母和数字',16,1);
        RETURN;
    END

    DECLARE
        @sql nvarchar(MAX),
        @loop int,
        @max_loop int,
        @TmpDictionary sysname = QUOTENAME('tmp.' + @ConsignmentNo + '_Dictionary'),
        @TmpThesaurus sysname = QUOTENAME('mail.' + @ConsignmentNo + '_Thesaurus')

    -- 动态获取循环起止ID
    SET @sql = N'
    SELECT @loop = MIN(ID) FROM ' + @TmpDictionary + N' WHERE [Source] = @Source;
    SELECT @max_loop = MAX(ID) FROM ' + @TmpDictionary + N' WHERE [Source] = @Source;
    '
    -- 绑定动态SQL输出参数到外层变量
    EXEC sp_executesql @sql,
        N'@Source varchar(max), @loop int OUTPUT, @max_loop int OUTPUT',
        @Source = @Source,
        @loop = @loop OUTPUT,
        @max_loop = @max_loop OUTPUT

    -- 循环处理数据
    WHILE @loop <= @max_loop 
    BEGIN
        SET @sql = N'
            UPDATE t
            -------------------
            FROM ' + @TmpThesaurus + N' t
            INNER JOIN ' + @TmpDictionary + N' d ON d.ID = @loop
            WHERE 1=1
            -------------------
        '
        PRINT (@sql)
        EXEC sp_executesql @sql, N'@loop int', @loop = @loop
                        
        SET @loop = @loop +1
    END
END
GO

• 数据库标识符（表名、列名、库名、Schema名）无法通过参数化方式传入动态SQL，必须做完合法性校验后拼接进SQL字符串。
• 优先使用sp_executesql而非EXEC()执行动态SQL，支持参数绑定，执行计划复用率更高，也能进一步降低SQL注入风险。
• 拼接标识符时必须用QUOTENAME()包裹，避免标识符含特殊字符（比如空格、保留关键字）时出现语法错误。

内容的提问来源于stack exchange，提问作者a.h