How to retrieve a variable name given its value?

How do I retrieve a particular value when the table name, column are given in a SQL Procedure

• declare @id int execute getLastRaw 'subjectID','tblSubject', @id output print @id alter procedure getLastRaw @column char(20), @tbl Char(20), @return int output as declare @dynSQL varchar(100) select @dynSQL ='SELECT TOP 1 '+@return+'='+ @column + ' FROM ' + @tbl + ' ORDER BY ' + @column + ' DESC' exec(@dynsQL) I want to get the value which gets selected from the select statement. But it says: Msg 245, Level 16, State 1, Procedure getLastRaw, Line 5 Conversion failed when converting the varchar value 'SELECT TOP 1 ' to data type int.

• Answer:

  When you concatenate @return SQL Server tries to convert the whole expression to int because of http://msdn.microsoft.com/en-us/library/ms190309.aspx You can't assign @return outside or inside of the dynamic SQL because of scope: the stored proc and the dynamic SQL are different. Local variables are local to the scope only You'll have to use a temp table: this is per connection and available for inner scopes (dynamic SQL) ... declare @dynSQL varchar(100) select @dynSQL ='SELECT TOP 1 '+ @column + ' FROM ' + @tbl + ' ORDER BY ' + @column + ' DESC' CREATE TABLE #result (rtn int) INSERT #result (rtn) exec(@dynsQL) SELECT @return = rtn FROM #result GO Although, it is just as easy and more correct to use a proper SELECT... declare @id int SELECT @id = subjectID FROM tblSubject ORDER BY subjectID DESC print @id