tags:

views:

1696

answers:

2

I'm trying to get the value of generated sql inside a stored procedure.
Executing this

   DECLARE @sSQL varchar(5000), @retval int
   DECLARE @tablename varchar(50)

   SELECT @tablename = 'products'

   SELECT @sSQL = 'SELECT @retval = MAX(ID)'
   SELECT @sSQL = @sSQL + ' FROM ' + @tablename
   EXEC (@sSQL) 

   SELECT @retval

I get

   Must declare the variable '@retval'.

How can I get the value without using a cursor (which I'm trying to avoid)?

+6  A: 

You will need to use sp_executesql:

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;
Mitch Wheat
A: 

You can simply this and do the following.

DECLARE @sSQL varchar(5000), @retval int
DECLARE @tablename varchar(50)

SELECT @tablename = 'products'

SELECT @sSQL = 'SELECT MAX(ID)'
SELECT @sSQL = @sSQL + ' FROM ' + @tablename
EXEC (@sSQL)
Mitchel Sellers
That doesn't do what the poster asked for.
Mitch Wheat
Correct, but the posters result, based on their code does exactly what mine does, except takes more steps to do it.
Mitchel Sellers
Obviously the code was shortened for clarity and brevity, but the question was "how to fill a **variable** ..."
Eduardo Molteni