Is there a way to get a value of a local variable specified by its name dynamically in SQL Server SP?
declare @foo int
declare @bar int
declare @variable_name varchar(10)
set @variable_name = '@foo'
print -- magic happens here - how to print the value of the variable
-- which name is stored in @variable_name, in this case @foo
eval won't help since it does not have access to local variables of the enclosing scope.
I'm doing this for debugging/diagnostics purposes mostly.
Technically this is possible by passing all local variables to sp_executesql:
declare @foo int
declare @bar int
declare @variable_name varchar(10)
set @variable_name = '@foo'
set @foo = 1;
set @bar = 2;
declare @sql nvarchar(max);
set @sql = N'SELECT ' + @variable_name;
exec sp_executesql @sql, N'@foo int, @bar int', @foo, @bar
Of course this would be quite hard to maintain in real life, the call to sp_executesql would have to be constantly kept up to date with the local variables on the current frame (batch or procedure call). While this is somewhat 'generic', is hardly any better than using a big CASE with all local variable names.
declare @foo int
declare @bar int
select @foo=1, @bar=2
declare @variable_name varchar(10)
set @variable_name = '@foo'
if @variable_name = '@foo'
print @foo
else if @variable_name = '@bar'
print @bar
