需使用动态SQL时，如何在SQL Server函数中使用临时表或表变量？

问题分析 #

你遇到的核心问题是：

• SQL Server函数不支持临时表，无法用临时表处理列数据；
• 表变量在动态SQL中无法访问，因为sp_executesql的执行上下文与外部批处理完全隔离，外部声明的表变量不在动态SQL的作用域内，这就是示例代码报错的原因。

解决方案 #

1. 动态SQL中替代表变量的方法

如果只是解决动态SQL访问临时数据的问题，可以用**局部临时表（#temp）**代替表变量，局部临时表在同一会话的所有批处理（包括动态SQL）中可见：

DECLARE @Table nvarchar(max) = 'WireColor'
DECLARE @Schema nvarchar(max) = 'Enum'
-- 创建局部临时表
CREATE TABLE #tempTable (ColumnName varchar(max))
INSERT INTO #tempTable (ColumnName)
SELECT Column_Name FROM INFORMATION_SCHEMA.Columns 
WHERE Table_Name = @Table AND TABLE_SCHEMA = @Schema

DECLARE @sp_select nvarchar(max) = 'SELECT * FROM ( 
    SELECT Row_Number() OVER (ORDER BY (Select NULL) ) AS rownumber, *
    FROM  #tempTable) sub WHERE rownumber = 1'

EXEC sp_executesql @sp_select
-- 用完后清理临时表
DROP TABLE #tempTable

但注意：用户定义函数中仍然不能使用临时表，所以这个方案只适用于触发器或存储过程中。

2. 审计场景的最优实现（绕过函数限制）

针对你要创建Changes审计表的需求，直接在触发器中实现逻辑比用函数更简单，还能避开函数的诸多限制。核心思路是将inserted/deleted表的行数据转成「列名-值」的结构，再对比新旧值记录变更：

第一步：创建Changes审计表

CREATE TABLE Changes (
    ChangeID INT IDENTITY(1,1) PRIMARY KEY,
    SchemaName NVARCHAR(128) NOT NULL,
    TableName NVARCHAR(128) NOT NULL,
    ColumnChanged NVARCHAR(128) NOT NULL,
    DeletedValue NVARCHAR(MAX),
    InsertedValue NVARCHAR(MAX),
    ChangeTime DATETIME DEFAULT GETDATE(),
    ChangedBy SYSNAME DEFAULT SUSER_SNAME()
)

第二步：为目标表创建审计触发器（以Enum.WireColor为例）

CREATE TRIGGER TR_WireColor_Audit
ON Enum.WireColor
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- 获取当前表的所有列名，用于UNPIVOT转换
    DECLARE @Columns NVARCHAR(MAX)
    SELECT @Columns = STRING_AGG(QUOTENAME(Column_Name), ',')
    FROM INFORMATION_SCHEMA.Columns
    WHERE Table_Schema = 'Enum' AND Table_Name = 'WireColor'

    -- 构造INSERT/UPDATE的新值查询语句
    DECLARE @InsertedSQL NVARCHAR(MAX) = N'
        SELECT 
            SchemaName = ''Enum'',
            TableName = ''WireColor'',
            ColumnChanged = ColumnName,
            DeletedValue = NULL,
            InsertedValue = Value
        FROM (
            SELECT ' + @Columns + ',
                   -- 用主键作为行唯一标识，这里假设ID是主键
                   RowID = CAST(ID AS NVARCHAR(MAX))
            FROM inserted
        ) t
        UNPIVOT (
            Value FOR ColumnName IN (' + @Columns + ')
        ) up
    '

    -- 构造DELETE/UPDATE的旧值查询语句
    DECLARE @DeletedSQL NVARCHAR(MAX) = N'
        SELECT 
            SchemaName = ''Enum'',
            TableName = ''WireColor'',
            ColumnChanged = ColumnName,
            DeletedValue = Value,
            InsertedValue = NULL
        FROM (
            SELECT ' + @Columns + ',
                   RowID = CAST(ID AS NVARCHAR(MAX))
            FROM deleted
        ) t
        UNPIVOT (
            Value FOR ColumnName IN (' + @Columns + ')
        ) up
    '

    -- 根据操作类型处理审计数据
    IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
    BEGIN
        -- UPDATE操作：只记录值发生变化的列
        DECLARE @UpdateSQL NVARCHAR(MAX) = N'
            WITH InsertedCTE AS (' + @InsertedSQL + '),
                 DeletedCTE AS (' + @DeletedSQL + ')
            SELECT 
                i.SchemaName,
                i.TableName,
                i.ColumnChanged,
                d.DeletedValue,
                i.InsertedValue
            FROM InsertedCTE i
            JOIN DeletedCTE d ON i.RowID = d.RowID AND i.ColumnChanged = d.ColumnChanged
            WHERE ISNULL(i.InsertedValue, '''') != ISNULL(d.DeletedValue, '''')
        '
        INSERT INTO Changes(SchemaName, TableName, ColumnChanged, DeletedValue, InsertedValue)
        EXEC sp_executesql @UpdateSQL
    END
    ELSE IF EXISTS(SELECT * FROM inserted)
    BEGIN
        -- INSERT操作：记录所有列的新值
        INSERT INTO Changes(SchemaName, TableName, ColumnChanged, DeletedValue, InsertedValue)
        EXEC sp_executesql @InsertedSQL
    END
    ELSE IF EXISTS(SELECT * FROM deleted)
    BEGIN
        -- DELETE操作：记录所有列的旧值
        INSERT INTO Changes(SchemaName, TableName, ColumnChanged, DeletedValue, InsertedValue)
        EXEC sp_executesql @DeletedSQL
    END
END

如果需要给多个表创建审计触发器，可以写一个存储过程遍历所有表，自动生成上述触发器脚本。

3. 替代方案：使用CDC（变更数据捕获）

如果你的SQL Server是企业版，可以开启变更数据捕获（CDC），它会自动捕获表的所有变更，不需要自己编写触发器和审计表，功能更完善且性能更优。

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