nick nick - 1 month ago 18
SQL Question

Pass table variable to exec SQL Server

I am trying to write this stored procedure but I have trouble passing result of temp table (or table variable) to

EXEC (@SQL)
task:

DECLARE @colsUnpivot AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
DECLARE @Table_Name as Varchar(200)
DECLARE @transaction_Table NVarchar(500)

SET @Table_Name = 'opd_scholar'
SET @transaction_Table = 'opd_scholar_transaction'

--DECLARE @TEMP TABLE(colsUnpivot varchar(max))
CREATE TABLE #TEMP (colsUnpivot varchar(max))

SELECT @colsUnpivot = 'SELECT
STUFF ( (
SELECT '', ''+ QUOTENAME(InTab.COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS InTab
WHERE InTab.TABLE_NAME = OutTab.TABLE_NAME
ORDER BY InTab.ORDINAL_POSITION
FOR XML PATH(''''), TYPE
).value(''.'',''VARCHAR(MAX)''
) , 1,1,SPACE(0))
FROM INFORMATION_SCHEMA.COLUMNS OutTab
WHERE TABLE_NAME = '''+ @Table_Name +'''
GROUP BY OutTab.TABLE_NAME'
--INSERT INTO @TEMP (colsUnpivot)
INSERT INTO #TEMP (colsUnpivot)
EXEC(@colsUnpivot)
PRINT @colsUnpivot
--SELECT colsUnpivot FROM @TEMP
SELECT colsUnpivot FROM #TEMP

DECLARE @TEMP1 NVARCHAR(MAX) = 'SELECT colsUnpivot FROM #TEMP'

--PRINT @TEMP

SET @query
= 'INSERT INTO '+ @transaction_Table +' ( unitid,institution,city,state,zip,code_name,lkp_value)
SELECT unitid,institution,city,state,zip,code_name,lkp_value
FROM
(
SELECT unitid,institution,city,state,zip, '+ @TEMP1+'
FROM '+@Table_name+') AS cp
UNPIVOT (lkp_value for code_name IN ('+@TEMP1+')
) AS up'
PRINT @Query
--PRINT @Query1
EXEC(@query)
DROP TABLE #TEMP


I also used table variable (see commented area) but not sure how this works.

Please copy and paste this code into your query window to get more understanding.
Thats right Hogan. I want the result of @colsUnpivot inside some variable so that I can pss that to my next segment of code where currently I am using @TEMP1.

Answer

Try this

DECLARE @colList VARCHAR(MAX)

SELECT @colList = 
  Stuff((
    Select ', ' + C.COLUMN_NAME
    From INFORMATION_SCHEMA.COLUMNS As C
    Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
        And C.TABLE_NAME = T.TABLE_NAME
    Order By C.ORDINAL_POSITION
    For Xml Path('')
 ), 1, 2, '') 
From INFORMATION_SCHEMA.TABLES As T
WHERE TABLE_NAME = @Table_Name
GROUP BY TABLE_NAME

and then use @colList instead of @TEMP1

You don't need a temporary table or any of the other madness.

(nb -- I got the column code from here http://stackoverflow.com/a/4936669/215752)