动态SQL存储过程无法填充临时表，求解决方案

DevOps 小满

2026-8-3

问题描述

我写了一个带@tablename参数的存储过程，用来生成指定表的属性信息表，调用方式如下：

EXEC sp_Schema_Presentation @tablename = 'UserID'

执行时碰到了这些问题：

我用@DynamicSQL字符串拼接@tablename参数，但里面的SELECT语句要创建#TEMP临时表
后续查询需要用到这个#TEMP表，所以我在存储过程开头就声明了它的结构
但执行完存储过程后，#TEMP表是空的
如果把@tablename硬编码进去，查询就能正常运行

相关代码片段：

CREATE TABLE #TEMP 
(
    SampleKey nvarchar(MAX), 
    SampleData nvarchar(MAX)
)

DECLARE @DynamicSQL NVARCHAR(MAX)
SET @DynamicSQL = N'SELECT B.*
INTO dbo.#TEMP
FROM (
     SELECT * FROM ' + @Tablename + N' ORDER BY 1 DESC
     OFFSET 1 ROWS
     FETCH NEXT 1 ROWS ONLY 
    ) A

    CROSS APPLY (
                  SELECT [Key] AS SampleKey
                  ,Value AS SampleData
                  FROM OpenJson( (SELECT A.* FOR JSON Path, Without_Array_Wrapper,INCLUDE_NULL_VALUES ) )

               ) B'

完整的SQL Server 2016存储过程代码：

ALTER PROCEDURE [dbo].[sp_Schema_Presentation]
    @TableName nvarchar(MAX)
AS
BEGIN
    CREATE TABLE #TEMP 
    (
         SampleKey nvarchar(MAX), 
         SampleData nvarchar(MAX)
    )

    DECLARE @DynamicSQL NVARCHAR(MAX)

    SET @DynamicSQL = N'SELECT B.*
                         INTO dbo.#TEMP
                         FROM (
                              SELECT * FROM ' + @Tablename + N' ORDER BY 1 DESC
                              OFFSET 1 ROWS
                              FETCH NEXT 1 ROWS ONLY
                              ) A
                         CROSS APPLY (
                                      SELECT [Key] AS SampleKey
                                      ,Value AS SampleData
                                      FROM OpenJson( (SELECT A.* FOR JSON Path, Without_Array_Wrapper,INCLUDE_NULL_VALUES ) )

                                      ) B'

       DECLARE @Columns as NVARCHAR(MAX)
       SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME(COLUMN_NAME)
         FROM
            (
              SELECT COLUMN_NAME FROM PRESENTATION_PP.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N''' + @TableName + '''
          ) AS B

        EXECUTE sp_executesql @DynamicSQL

        SELECT a.COLUMN_NAME,
                     CASE WHEN a.COLUMN_NAME LIKE '%[_]_key' THEN a.COLUMN_NAME
                           ELSE REPLACE(a.COLUMN_NAME,'_',' ') END AS DISPLAY_NAME,
                           a.DATA_TYPE, COALESCE(a.CHARACTER_MAXIMUM_LENGTH,  a.NUMERIC_PRECISION) AS SIZE,
                     CASE WHEN NUMERIC_SCALE IS NULL THEN 0
                           ELSE NUMERIC_SCALE END AS SCALE,
                           a.IS_NULLABLE AS NULLABLE,
                     CASE WHEN i.is_primary_key IS NOT NULL THEN 'YES'
                           ELSE 'NO' END AS PK,
                     #TEMP.SampleData
       FROM PRESENTATION_PP.INFORMATION_SCHEMA.COLUMNS a
                     LEFT JOIN
                           sys.columns c ON a.COLUMN_NAME = c.name
                     LEFT JOIN
                           sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                     LEFT JOIN
                           sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
                     LEFT JOIN
                           #TEMP ON a.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AI = #TEMP.SampleKey COLLATE SQL_Latin1_General_CP1_CI_AI
       WHERE TABLE_NAME =  @TableName AND c.object_id = OBJECT_ID(@TableName)
        SELECT * FROM #TEMP
       DROP TABLE #TEMP

END

解决办法

问题核心：动态SQL里用INTO dbo.#TEMP会新建一个独立的局部临时表，和你在存储过程开头创建的#TEMP不是同一个对象，所以外部的#TEMP始终为空。

修正方案：

把动态SQL里的INTO dbo.#TEMP改成INSERT INTO #TEMP，直接往预先创建的临时表里插数据
用QUOTENAME()包裹表名，避免SQL注入风险
修复@Columns拼接时的字符串错误（多了一层单引号）

修改后的完整存储过程：

ALTER PROCEDURE [dbo].[sp_Schema_Presentation]
    @TableName nvarchar(MAX)
AS
BEGIN
    SET NOCOUNT ON; -- 避免返回额外的行数信息

    CREATE TABLE #TEMP 
    (
         SampleKey nvarchar(MAX), 
         SampleData nvarchar(MAX)
    )

    DECLARE @DynamicSQL NVARCHAR(MAX)

    -- 改用INSERT INTO，并用QUOTENAME处理表名防注入
    SET @DynamicSQL = N'INSERT INTO #TEMP (SampleKey, SampleData)
                         SELECT B.*
                         FROM (
                              SELECT * FROM ' + QUOTENAME(@Tablename) + N' ORDER BY 1 DESC
                              OFFSET 1 ROWS
                              FETCH NEXT 1 ROWS ONLY
                              ) A
                         CROSS APPLY (
                                      SELECT [Key] AS SampleKey
                                      ,Value AS SampleData
                                      FROM OpenJson( (SELECT A.* FOR JSON Path, Without_Array_Wrapper,INCLUDE_NULL_VALUES ) )
                                      ) B'

    DECLARE @Columns as NVARCHAR(MAX)
    -- 修复字符串拼接错误，去掉多余的单引号
    SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME(COLUMN_NAME)
    FROM PRESENTATION_PP.INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = @TableName

    EXECUTE sp_executesql @DynamicSQL

    SELECT a.COLUMN_NAME,
           CASE WHEN a.COLUMN_NAME LIKE '%[_]_key' THEN a.COLUMN_NAME
                ELSE REPLACE(a.COLUMN_NAME,'_',' ') END AS DISPLAY_NAME,
           a.DATA_TYPE, 
           COALESCE(a.CHARACTER_MAXIMUM_LENGTH, a.NUMERIC_PRECISION) AS SIZE,
           CASE WHEN NUMERIC_SCALE IS NULL THEN 0
                ELSE NUMERIC_SCALE END AS SCALE,
           a.IS_NULLABLE AS NULLABLE,
           CASE WHEN i.is_primary_key IS NOT NULL THEN 'YES'
                ELSE 'NO' END AS PK,
           t.SampleData
    FROM PRESENTATION_PP.INFORMATION_SCHEMA.COLUMNS a
    LEFT JOIN sys.columns c ON a.COLUMN_NAME = c.name
    LEFT JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    LEFT JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    LEFT JOIN #TEMP t ON a.COLUMN_NAME COLLATE SQL_Latin1_General_CP1_CI_AI = t.SampleKey COLLATE SQL_Latin1_General_CP1_CI_AI
    WHERE TABLE_NAME = @TableName 
      AND c.object_id = OBJECT_ID(@TableName)

    SELECT * FROM #TEMP

    DROP TABLE #TEMP
END

补充说明：

局部临时表#TEMP在存储过程主上下文和动态SQL执行上下文里是共享的，所以INSERT INTO能直接把数据写入预先创建的表中
QUOTENAME()会给表名加上方括号，处理表名含特殊字符/关键字的情况，同时防止SQL注入
SET NOCOUNT ON;让存储过程输出更简洁，不会返回额外的"影响行数"信息

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