tsql - How to get sp_executesql result into a variable?

ID : 20258

viewed : 33

Tags : tsqldynamic-sqltsql

Top 5 Answer for tsql - How to get sp_executesql result into a variable?

vote vote

97

If you have OUTPUT parameters you can do

DECLARE @retval int    DECLARE @sSQL nvarchar(500); DECLARE @ParmDefinition nvarchar(500);  DECLARE @tablename nvarchar(50)   SELECT @tablename = N'products'    SELECT @sSQL = N'SELECT @retvalOUT = MAX(ID) FROM ' + @tablename;   SET @ParmDefinition = N'@retvalOUT int OUTPUT';  EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;  SELECT @retval; 

But if you don't, and can not modify the SP:

-- Assuming that your SP return 1 value create table #temptable (ID int null) insert into #temptable exec mysp 'Value1', 'Value2' select * from #temptable 

Not pretty, but works.

vote vote

89

DECLARE @vi INT DECLARE @vQuery NVARCHAR(1000)  SET @vQuery = N'SELECT @vi= COUNT(*) FROM <TableName>'  EXEC SP_EXECUTESQL          @Query  = @vQuery       , @Params = N'@vi INT OUTPUT'       , @vi = @vi OUTPUT  SELECT @vi 
vote vote

74

DECLARE @tab AS TABLE (col1 VARCHAR(10), col2 varchar(10))  INSERT into @tab EXECUTE  sp_executesql N' SELECT 1 AS col1, 2 AS col2 UNION ALL SELECT 1 AS col1, 2 AS col2 UNION ALL SELECT 1 AS col1, 2 AS col2'  SELECT * FROM @tab 
vote vote

66

Return values are generally not used to "return" a result but to return success (0) or an error number (1-65K). The above all seem to indicate that sp_executesql does not return a value, which is not correct. sp_executesql will return 0 for success and any other number for failure.

In the below, @i will return 2727

DECLARE @s NVARCHAR(500) DECLARE @i INT; SET @s = 'USE [Blah]; UPDATE STATISTICS [dbo].[TableName] [NonExistantStatisticsName];'; EXEC @i = sys.sp_executesql @s SELECT @i AS 'Blah' 

SSMS will show this Msg 2727, Level 11, State 1, Line 1 Cannot find index 'NonExistantStaticsName'.

vote vote

57

If you want to return more than 1 value use this:

DECLARE @sqlstatement2      NVARCHAR(MAX); DECLARE @retText            NVARCHAR(MAX);   DECLARE @ParmDefinition     NVARCHAR(MAX); DECLARE @retIndex           INT = 0;  SELECT @sqlstatement = 'SELECT @retIndexOUT=column1 @retTextOUT=column2 FROM XXX WHERE bla bla';  SET @ParmDefinition = N'@retIndexOUT INT OUTPUT, @retTextOUT NVARCHAR(MAX) OUTPUT';  exec sp_executesql @sqlstatement, @ParmDefinition, @retIndexOUT=@retIndex OUTPUT, @retTextOUT=@retText OUTPUT; 

returned values are in @retIndex and @retText

Top 3 video Explaining tsql - How to get sp_executesql result into a variable?

Related QUESTION?