I have a piece of dynamic SQL I need to execute, I then need to store the result into a variable.
I know I can use sp_executesql
but can't find clear examples around about how to do this.
I have a piece of dynamic SQL I need to execute, I then need to store the result into a variable.
I know I can use sp_executesql
but can't find clear examples around about how to do this.
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.
select @variable = exec proc_name
not entirely sure on the syntax but that should be pretty close.
DECLARE @vi INT DECLARE @vQuery VARCHAR(1000)
SET @vQuery = 'SELECT @vi= COUNT(*) FROM '
EXEC SP_EXECUTESQL
@Query = @vQuery
, @Params = N'@viINT OUTPUT'
, @vi= @viOUTPUT
SELECT @vi