Dynamic Query to Print out the resultset from a table

Question

Dynamic Query to Print out the resultset from a table

SQL006

SSCertifiable

Points: 5071
More actions
September 9, 2015 at 7:36 am

#307596

I wan to print out the dynamic query result so that i can use as a script for some tasks.
This is the scenario wher i got stuck, i am not able to print out the result as it return only the last value because of OUTPUT param limitation
Is there any way to print all the 3 INSERT stmt.
IF OBJECT_ID ('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp (Command varchar(8000))
INSERT INTO #temp
SELECT 'INSERT INTO Test1(column1,column2)values(1,2)'
UNION ALL
SELECT 'INSERT INTO Test2(column1,column2)values(1,2)'
UNION ALL
SELECT 'INSERT INTO Test3(column1,column2)values(1,2)'
DECLARE @Column_string varchar(max)
DECLARE @Column_string_Out varchar(max)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = 'SELECT @Column_string = Command FROM #temp'
--PRINT(@SQL)
EXECUTE sp_executesql @SQL,N'@Column_string varchar(max) OUTPUT',@Column_string = @Column_string_Out Output
--EXEC sys.sp_executesql @SQL
PRINT(@Column_string_Out)
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

Why don't you simply display the results as text instead of a grid?

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

SQL006 SSCertifiable Points: 5071 More actions · Answer 2

Luis Cazares (9/9/2015)
Why don't you simply display the results as text instead of a grid?

Thanks for the reply.. I am trying to generate automated script which contain

BEGIN TRANS

Insert stmt-- this can be generated from multiple table in the above example i am using only 1 temp table

Commit trans

Rollback

If i use the grid it displays column name from table which i don't want

Command

------------------------------------------------------------------------------------------------------------------

INSERT INTO Test1(column1,column2)values(1,2)

INSERT INTO Test2(column1,column2)values(1,2)

INSERT INTO Test3(column1,column2)values(1,2)

anyway to remove those column name and lines from the result.

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

Sorry, I understood that you needed to print, copy and execute the code.

You can concatenate all the commands in a single string. This article explains how to do it: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

DECLARE @Column_string_Out varchar(max);

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = 'SELECT @Column_string = (SELECT Command + '';'' + CHAR(13) '

+ 'FROM #temp '

+ 'FOR XML PATH(''''),TYPE).value(''.'', ''varchar(max)'')';

--PRINT(@SQL);

EXECUTE sp_executesql @SQL,N'@Column_string varchar(max) OUTPUT',@Column_string = @Column_string_Out Output;

PRINT(@Column_string_Out);

You can also do it inside a cursor. In this case, you'll be processing one row at a time anyway, so using a cursor is not a bad idea.

DECLARE Commands CURSOR LOCAL STATIC FORWARD_ONLY

FOR

SELECT Command + ';'

FROM #temp;

OPEN Commands;

FETCH NEXT FROM Commands INTO @SQL;

WHILE @@FETCH_STATUS = 0

BEGIN

--EXEC sys.sp_executesql @SQL;

PRINT @SQL;

FETCH NEXT FROM Commands INTO @SQL;

END

CLOSE Commands;

DEALLOCATE Commands;

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

SQL006 SSCertifiable Points: 5071 More actions · Answer 4

SQL006

SSCertifiable

Points: 5071

September 9, 2015 at 11:01 pm

#1825581

Thanks Luis for the solution