动态 SQL 存储过程不填充临时表

我使用一个带有一个参数(@tablename)的存储过程来生成一个关于通过该参数命名的表的属性表.

我按如下方式调用存储过程

EXEC sp_Schema_Presentation @tablename = 'UserID'

并运行存储过程(在本文的底部).

• 为了使用我的@tablename参数，我已经创建了一个@DynamicSQL字符串.但是，使用它的SELECT语句也创建了#TEMP表.
• 查询的其余部分使用这个#TEMP表，因此我将其 struct DECLARE放在顶部.
• 然而，当我运行存储过程时，#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