SQL Server动态SQL表名语法错误排查求助

在SQL Server中编写存储过程，用于检查指定表中是否存在目标数据：不存在则终止执行，存在则向目标表插入数据。但通过变量传递数据库名时触发错误：

Msg 208, Level 16, State 1, Line 65
Invalid object name 'EMR-Integration-DEV.dbo.EMR_ADF_Variables_test'

存储过程代码如下：

CREATE PROCEDURE InsertData
    @Client_Id INT,
    @EMR_Id INT,
    @Client_Name NVARCHAR(255),
    @EMR_Name NVARCHAR(255),
    @DB_Name NVARCHAR(255)
AS
BEGIN
    DECLARE @Full_Table_name NVARCHAR(255);
    SET @Full_Table_name = @DB_Name + '.dbo.Variable_Table_Name'; 

    -- Check if values exist in tables in the Reference database
    IF NOT EXISTS (SELECT 1 FROM Dim.Client_table WHERE ClientId = @Client_Id AND StagedName = @Client_Name)
    BEGIN
        PRINT 'Error: Client does not exist.';
        RETURN;
    END

    IF NOT EXISTS (SELECT 1 FROM  [dbo].[EMR] WHERE EMR_Id = @EMR_Id AND EMR_Name = @EMR_Name)
    BEGIN
        PRINT 'Error: EMR does not exist.';
        RETURN;
    END
    
    DECLARE @SqlQuery NVARCHAR(MAX);

    SET @SqlQuery = 
        'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(@Full_Table_name) + ' WHERE Client_Id = ' + CAST(@Client_Id AS NVARCHAR) + ' AND EMR_Id = ' + CAST(@EMR_Id AS NVARCHAR) + ')
        BEGIN
            INSERT INTO ' + QUOTENAME(@Full_Table_name) + ' (Client_Id, EMR_Id, Client_Name, EMR_Name)
            VALUES (' + CAST(@Client_Id AS NVARCHAR) + ', ' + CAST(@EMR_Id AS NVARCHAR) + ', N''' + @Client_Name + ''', N''' + @EMR_Name + ''');
            INSERT INTO ' + QUOTENAME(@Full_Table_name) + ' (Client_Id, EMR_Id, Client_Name, EMR_Name)
            VALUES (' + CAST(@Client_Id AS NVARCHAR) + ', ' + CAST(@EMR_Id AS NVARCHAR) + ', N''' + @Client_Name + ''', N''' + @EMR_Name + ''');
        END';

    EXEC sp_executesql @SqlQuery;

    PRINT 'Data inserted successfully.';
END

尝试调整单引号修正数据库名格式后问题仍未解决，请求排查。

错误的核心是**QUOTENAME的使用方式错误**：你将数据库名.架构.表名的完整字符串传入QUOTENAME，这会让SQL Server把整个字符串识别为单一对象名（例如[EMR-Integration-DEV.dbo.EMR_ADF_Variables_test]），但SQL Server要求对数据库名、架构名、表名分别进行转义，正确格式应为[EMR-Integration-DEV].[dbo].[EMR_ADF_Variables_test]。

此外，当前代码还存在两个隐患：

• 直接拼接字符串到动态SQL中，存在SQL注入风险（尤其是@Client_Name、@EMR_Name这类字符串参数）
• 拼接INT类型参数时，虽暂时安全，但不符合最佳实践

修改存储过程，分别对数据库名、架构名、表名使用QUOTENAME，同时改用参数化动态SQL避免注入风险：

CREATE PROCEDURE InsertData
    @Client_Id INT,
    @EMR_Id INT,
    @Client_Name NVARCHAR(255),
    @EMR_Name NVARCHAR(255),
    @DB_Name NVARCHAR(255)
AS
BEGIN
    -- 检查基础数据是否存在
    IF NOT EXISTS (SELECT 1 FROM Dim.Client_table WHERE ClientId = @Client_Id AND StagedName = @Client_Name)
    BEGIN
        PRINT 'Error: Client does not exist.';
        RETURN;
    END

    IF NOT EXISTS (SELECT 1 FROM [dbo].[EMR] WHERE EMR_Id = @EMR_Id AND EMR_Name = @EMR_Name)
    BEGIN
        PRINT 'Error: EMR does not exist.';
        RETURN;
    END
    
    DECLARE @SqlQuery NVARCHAR(MAX);
    -- 分别转义数据库、架构、表名
    DECLARE @QuotedDBName NVARCHAR(256) = QUOTENAME(@DB_Name);
    DECLARE @QuotedSchema NVARCHAR(256) = QUOTENAME('dbo');
    DECLARE @QuotedTableName NVARCHAR(256) = QUOTENAME('Variable_Table_Name');
    DECLARE @FullQualifiedName NVARCHAR(MAX) = @QuotedDBName + '.' + @QuotedSchema + '.' + @QuotedTableName;

    -- 使用参数化动态SQL，避免注入
    SET @SqlQuery = 
        N'IF NOT EXISTS (SELECT 1 FROM ' + @FullQualifiedName + N' WHERE Client_Id = @Client_Id AND EMR_Id = @EMR_Id)
        BEGIN
            INSERT INTO ' + @FullQualifiedName + N' (Client_Id, EMR_Id, Client_Name, EMR_Name)
            VALUES (@Client_Id, @EMR_Id, @Client_Name, @EMR_Name),
                   (@Client_Id, @EMR_Id, @Client_Name, @EMR_Name); -- 合并INSERT语句简化代码
        END';

    -- 传递参数给sp_executesql
    EXEC sp_executesql @SqlQuery,
        N'@Client_Id INT, @EMR_Id INT, @Client_Name NVARCHAR(255), @EMR_Name NVARCHAR(255)',
        @Client_Id = @Client_Id,
        @EMR_Id = @EMR_Id,
        @Client_Name = @Client_Name,
        @EMR_Name = @EMR_Name;

    PRINT 'Data inserted successfully.';
END

1. 正确处理对象名转义：将数据库名、架构名、表名分别用QUOTENAME包裹后再拼接，确保带特殊字符（如连字符）的数据库名被SQL Server正确识别。
2. 参数化动态SQL：通过sp_executesql的参数传递功能直接传入变量，既避免了SQL注入风险，也无需手动处理字符串的单引号转义问题。
3. 简化INSERT语句：将两次独立的INSERT合并为一次，使用多行VALUES语法提升代码简洁性。

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