Pass table variable to exec SQL Server

Question

Post reply

Pass table variable to exec SQL Server

dallas13

SSCertifiable

Points: 7491
More actions
October 25, 2016 at 10:31 am

#314755

I am trying to write this stored procedure but I have trouble passing result of temp table (or table variable) to EXEC(@query) task:
I also used table variable (see commented area) but not luck.
I want the result of @colsUnpivot inside some variable so that I can pass that to my next segment of code where currently I am using @TEMP1.
Please copy and paste this code into your query window to get more understanding.
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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

it looks like your problem lies around here:

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'

since @TEMP1 will be the text 'SELECT colsUnpivot FROM #TEMP' when you concatenate it into the middle of the SELECT statement below it you essentially have:

SELECT unitid,institution,city,state,zip, SELECT colsUnpivot FROM #TEMP

FROM opd_scholar

drew.allen SSC Guru Points: 76944 More actions · Answer 2

You really should be using sp_execute_sql rather than EXEC (). sp_execute_sql will allow you to pass in parameters, which helps to protect against SQL injection.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Try changing your code to this, there's no need for all that dynamic code:

DECLARE @colsUnpivot AS NVARCHAR(MAX);

DECLARE @query AS NVARCHAR(MAX);

DECLARE @Table_Name as Varchar(200);

DECLARE @transaction_Table NVarchar(500);

SET @Table_Name = 'employees';

SET @transaction_Table = 'opd_scholar_transaction';

SELECT @colsUnpivot = STUFF ( (

SELECT ', '+ QUOTENAME(InTab.COLUMN_NAME)

FROM INFORMATION_SCHEMA.COLUMNS InTab

WHERE InTab.TABLE_NAME = @Table_Name

ORDER BY InTab.ORDINAL_POSITION

FOR XML PATH(''), TYPE

).value('.','VARCHAR(MAX)'

) , 1,1,SPACE(0));

SET @query = 'INSERT INTO '+ QUOTENAME(@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, ' + @colsUnpivot + '

FROM '+ QUOTENAME(@Table_name) + ') AS cp

UNPIVOT (lkp_value for code_name IN ('+@colsUnpivot+')

) AS up' ;

PRINT @Query;

EXEC sp_executesql @query;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

dallas13 SSCertifiable Points: 7491 More actions · Answer 4

Thanks everyone.

Thanks Luis. That is very simple and it worked. 🙂

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

Thank you for the feedback.

I hope that you noticed the change in the table name. I also hope that you understand the solution and how does it work.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

dallas13 SSCertifiable Points: 7491 More actions · Answer 6

dallas13

SSCertifiable

Points: 7491

October 25, 2016 at 12:31 pm

#1908733

Yeah Louis. I understood those parts correctly.