Dynamic SQL stored procedure not populating temporary table

Question

I am using a stored procedure with one parameter (@tablename) to generate a table of attributes about the table named via the parameter.

I call the stored procedure as follows

EXEC sp_Schema_Presentation @tablename = 'UserID'

And run the stored procedure (at the bottom of this post).

• I have created a @DynamicSQL string in order to use my @tablename parameter. However, the SELECT statement, in which it's used, also creates the #TEMP table.
• The rest of the query uses this #TEMP table so I DECLARE its structure at the top.
• However, when I run the stored procedure, the #TEMP table is empty

If I hard code the @tablename, the query will work. Any ideas how I can fix this?

Thanks

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'

Full stored procedure in 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

Answer 1

Create the #Temp table first, and then INSERT INTO not Select ... Into #Temp

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

DECLARE @DynamicSQL NVARCHAR(MAX)
SET @DynamicSQL = N'
Insert Into #Temp
SELECT B.*
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
'

Exec(@DynamicSQL)


Select * from #Temp