Get a value of a variable dynamically in SQL Server SP

Question

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.

Answer 1

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

Answer 2

AFAIK, no: there is no direct way of doing this on SQL Server.

Answer 3

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.