views:

2768

answers:

3

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.

+6  A: 

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.

Eduardo Molteni
more or less yes - but I don't have params in the query, I was hoping to be able to run a simple select and store the output in a variable
JohnIdol
Do you mean that you want to stored several columns and rows in a single variable?
Eduardo Molteni
no :) - I am returning a single value smt like [select count(*) from whatever], another sp is generating that query and I don't have params embedded in the query
JohnIdol
Ok, I have completed the answer.
Eduardo Molteni
my sp will be sp_executesql @myQuery
JohnIdol
A: 

select @variable = exec proc_name

not entirely sure on the syntax but that should be pretty close.

DForck42
I am afraid that won't do it - I wish it was that easy, maybe I am missing some syntax detail
JohnIdol
A: 

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

Buchaiah