September 9, 2015 at 7:36 am
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
September 9, 2015 at 7:50 am
Why don't you simply display the results as text instead of a grid?
September 9, 2015 at 8:04 am
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.
September 9, 2015 at 8:32 am
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;
September 9, 2015 at 11:01 pm
Thanks Luis for the solution
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy