使用sp_executesql时出现「必须声明标量变量@OldValueOut」错误

你遇到的错误看似是变量未声明，但实际上是动态SQL拼接和执行中的几个关键逻辑问题导致的，咱们一步步拆解并修复：

核心问题分析 #

• 主键值拼接错误：你在拼接@sqlDeleted和@sqlInserted时，用了CONVERT(VARCHAR(100), @pkName)，这会把列名（比如SubSpace_Id）直接拼到SQL里，变成WHERE [SubSpace_Id] = SubSpace_Id，根本不是你需要的主键值匹配逻辑。
• 动态SQL执行变量混用：执行@sqlInserted的时候，你错误调用了@sqlDeleted变量，直接导致逻辑混乱。
• 变量类型与参数不匹配：你声明的@oldValue是VARCHAR(100)，但sp_executesql里定义的参数是NVARCHAR(100)，虽然能兼容，但保持类型一致能避免潜在问题。
• 审计日志插入SQL拼接错误：GETDATE()是函数，直接拼到字符串里会报错；'1234'作为字符串值，拼接时也没正确包裹单引号。
• 未处理INSERT/DELETE特殊场景：INSERT操作时#deleted为空，旧值应为NULL；DELETE操作时#inserted为空，新值应为NULL，原代码没做这些判断。

修正后的触发器代码 #

ALTER TRIGGER [dbo].[Lab_SubSpace_Contact_Changed] 
ON [dbo].[Lab_SubSpace_Contact] 
AFTER INSERT,DELETE,UPDATE 
AS 
BEGIN 
    SET NOCOUNT ON; 

    DECLARE @sql NVARCHAR(4000), 
            @sqlDeleted NVARCHAR(4000), 
            @sqlInserted NVARCHAR(4000), 
            @columns NVARCHAR(4000), 
            @item NVARCHAR(80), 
            @pos INT, 
            @oldValue NVARCHAR(100), 
            @newValue NVARCHAR(100), 
            @tableName NVARCHAR(80), 
            @pkName NVARCHAR(80),
            @pkValue NVARCHAR(100); -- 新增：存储当前操作的主键值

    SET @tableName = 'Lab_SubSpace_Contact'; 
    SET @pkName = 'SubSpace_Id'; 

    -- 获取主键值（假设为单主键，复合主键需调整逻辑）
    SELECT @pkValue = CONVERT(NVARCHAR(100), ISNULL(i.[SubSpace_Id], d.[SubSpace_Id]))
    FROM inserted i
    FULL JOIN deleted d ON i.[SubSpace_Id] = d.[SubSpace_Id];

    -- 创建临时表存储变更数据
    SELECT * INTO #deleted FROM deleted; 
    SELECT * INTO #inserted from inserted; 

    -- 根据操作类型获取需要审计的列
    IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
    BEGIN
        -- UPDATE操作：仅获取实际更新的列
        SET @columns = STUFF((SELECT ',' + QUOTENAME(name) 
                              FROM sys.columns 
                              WHERE object_id = OBJECT_ID(@tableName) 
                                AND SUBSTRING(COLUMNS_UPDATED(), ((column_id - 1) / 8 + 1), 1) 
                                & POWER(2, ((column_id - 1) % 8)) > 0 
                              FOR XML PATH('')), 1, 1, '');
    END
    ELSE
    BEGIN
        -- INSERT/DELETE操作：获取所有列
        SET @columns = STUFF((SELECT ',' + QUOTENAME(name) 
                              FROM sys.columns 
                              WHERE object_id = OBJECT_ID(@tableName) 
                              FOR XML PATH('')), 1, 1, '');
    END

    WHILE LEN(@columns) > 0 
    BEGIN 
        SET @pos = CHARINDEX(',', @columns); 
        IF @pos = 0 
            SET @item = @columns; 
        ELSE 
            SET @item = SUBSTRING(@columns, 1, @pos - 1); 

        -- 初始化旧值和新值
        SET @oldValue = NULL;
        SET @newValue = NULL;

        -- 获取旧值（仅DELETE/UPDATE场景有效）
        IF EXISTS(SELECT * FROM #deleted)
        BEGIN
            SET @sqlDeleted = N'SELECT @OldValueOut = ' + @item + ' FROM #deleted WHERE ' + QUOTENAME(@pkName) + ' = @PkValue';
            EXECUTE sp_executesql @sqlDeleted, 
                                 N'@OldValueOut NVARCHAR(100) OUTPUT, @PkValue NVARCHAR(100)', 
                                 @OldValueOut = @oldValue OUTPUT,
                                 @PkValue = @pkValue;
        END

        -- 获取新值（仅INSERT/UPDATE场景有效）
        IF EXISTS(SELECT * FROM #inserted)
        BEGIN
            SET @sqlInserted = N'SELECT @NewValueOut = ' + @item + ' FROM #inserted WHERE ' + QUOTENAME(@pkName) + ' = @PkValue';
            EXECUTE sp_executesql @sqlInserted, 
                                 N'@NewValueOut NVARCHAR(100) OUTPUT, @PkValue NVARCHAR(100)', 
                                 @NewValueOut = @newValue OUTPUT,
                                 @PkValue = @pkValue;
        END

        -- 判断值是否发生变更（包含NULL值的情况）
        IF (@oldValue <> @newValue) OR (@oldValue IS NOT NULL AND @newValue IS NULL) OR (@oldValue IS NULL AND @newValue IS NOT NULL)
        BEGIN 
            -- 使用参数化SQL插入审计日志，避免注入和类型错误
            SET @sql = N'INSERT INTO [dbo].[Audit_Log] (Table_Name, Primary_Key, Column_Name, Old_Value, New_Value, Modified, Updated_By_WWID) 
                        VALUES (@TableName, @PkValue, @ColumnName, @OldValue, @NewValue, GETDATE(), @UpdatedBy)';
            
            EXECUTE sp_executesql @sql,
                                 N'@TableName NVARCHAR(80), @PkValue NVARCHAR(100), @ColumnName NVARCHAR(80), 
                                   @OldValue NVARCHAR(100), @NewValue NVARCHAR(100), @UpdatedBy NVARCHAR(10)',
                                 @TableName = @tableName,
                                 @PkValue = @pkValue,
                                 @ColumnName = REPLACE(REPLACE(@item, '[', ''), ']', ''), -- 去掉列名的方括号
                                 @OldValue = @oldValue,
                                 @NewValue = @newValue,
                                 @UpdatedBy = '1234';
        END

        -- 移除已处理的列，进入下一轮循环
        IF @pos = 0 
            SET @columns = ''; 
        ELSE 
            SET @columns = SUBSTRING(@columns, @pos + 1, LEN(@columns) - @pos); 
    END ; 

    DROP TABLE #inserted; 
    DROP TABLE #deleted; 
END 

关键修改说明 #

• 新增主键值变量：@pkValue存储当前操作的主键实际值，彻底解决了之前拼列名的错误。
• 分场景处理变更列：针对INSERT/DELETE/UPDATE分别获取对应列，确保不会遗漏需要审计的内容。
• 修复动态SQL执行错误：执行@sqlInserted时使用正确的变量，同时通过参数传递主键值，避免SQL注入。
• 参数化插入审计日志：不再直接拼接SQL字符串，改用sp_executesql参数传递，既安全又解决了日期、字符串的类型拼接问题。
• 完善值比较逻辑：处理了NULL值的特殊情况，确保旧值或新值为NULL时也能正确识别变更。

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