How to get sp_executesql result into a variable?
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'.
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
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
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.