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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 9, 2015 at 7:50 am

  #1825400

  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

  September 9, 2015 at 8:04 am

  #1825404

  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: 183706

  More actions

  September 9, 2015 at 8:32 am

  #1825409

  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

  September 9, 2015 at 11:01 pm

  #1825581

  Thanks Luis for the solution