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