Get the return result of dynamic SQL

1. Introduction Notes

Sometimes after executing a stored procedure, you need to get the list returned by the stored procedure, and then do the appropriate operation, or execute the dynamic statement, get the return result of the situation, through Exec, sp_executesql can implement the function.

Online also have a lot of relevant reading, here to make a summary, convenient for later access to use

2. Build a table test script

IF object_id (' Tbscore ') is not NULL     DROP table tbscoregocreate table Tbscore    (      name VARCHAR),      course Varcha R (Ten),      fractional INT,      date datetime    ) Goinsert into  tbscore VALUES  (' Zhang San ', ' language ', 74,getdate ()) INSERT  into Tbscore values  (' Zhang San ', ' physical ', ', ', ' GETDATE () ') INSERT into  tbscore values  (' John Doe ', ' language ',, GETDATE ()) in SERT into  tbscore values  (' John Doe ', ' math ',, GETDATE ()) INSERT into  tbscore values  (' John Doe ', ' physical ', 94, GET DATE ()) GO

3. Example Demo

2.1 Executing dynamic statements with exec returns results

CREATE TABLE #tmpResult    (      name varchar (),      course VARCHAR (Ten),      fractional INT,      date datetime    ) Godeclare @ SQL1 VARCHAR SET @SQL1 = ' SELECT [name],[course],[score],[Date] from Tbscore '--write the results of EXEC execution to the temp table insert into #tmpResult EXEC (@ SQL1) SELECT * from #tmpResult--Clears the temporary table if object_id (' tempdb. #tmpResult ') is not nullbegindrop TABLE #tmpResultEND

2.2 Getting return values by executing dynamic statements with sp_executesql　

CREATE TABLE #tmpResult    (      name varchar (),      course VARCHAR (Ten),      fractional INT,      date datetime    ) Godeclare @ SQL2 NVARCHAR (+) DECLARE @Count intset @Count = 0--(1) Gets a single return value set @SQL2 = ' SELECT @RowNum = Count (0) from Tbscore ' EXEC sp _executesql @SQL2, N ' @RowNum INT output ', @Count output SELECT @Count    --(2) Get list return value Delete from #tmpResult--empty data set first SQL2 = ' SELECT [name],[course],[score],[Date] from Tbscore ' INSERT to #tmpResult EXEC sp_executesql @SQL2SELECT * from #tmpResult-- The temp table if object_id (' tempdb. #tmpResult ') is not nullbegindrop TABLE #tmpResultEND

　　

4. References

Http://www.cnblogs.com/yinhaiming/articles/1544922.html

Get the return result of dynamic SQL