Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TSQL: How to fill a variable using a generated sql command string without a cursor

Tags:

tsql

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)?

like image 998
Eduardo Molteni Avatar asked Dec 30 '22 08:12

Eduardo Molteni


1 Answers

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;
like image 104
Mitch Wheat Avatar answered Jan 14 '23 11:01

Mitch Wheat