5

Is there a way to convert @my_variable string into a value of @my_variable?

I have a table which stores names of variables. I need to get the value of this variable. Something like this:

DECLARE @second_variable AS NVARCHAR(20);
DECLARE @first_variable AS NVARCHAR(20);
SET @first_variable = '20';
SET @second_variable = SELECT '@first_variable'; --here I want that @second variable be assigned a value of "20".
iamdave
  • 12,023
  • 3
  • 24
  • 53
Markus
  • 3,547
  • 10
  • 39
  • 55

3 Answers3

1

The most likely solution to your problem is to address/change/fix the design that requires why you would have a table that stores the names of variables.

Harshvardhan
  • 479
  • 1
  • 3
  • 12
KM.
  • 101,727
  • 34
  • 178
  • 212
1

You can use sp_executesql to execute strings (so you could do something like sp_executesql 'SET @second_variable = ' + @name_of_variable;). However, just putting that one statement into sp_executesql won't work, since strings executed with sp_executesql or EXECUTE have their own variable scope, and, therefore, neither @second_variable nor @first_variable are accessible.

So, what you could do would be to move your complete code (excluding the name of the variable) into a sp_executesql statement (untested):

DECLARE @name_of_variable NVARCHAR(MAX)
SET @name_of_variable = ...

EXECUTE sp_executesql N'...lots of code... ' + @name_of_variable + ' ...lots of code...'
Heinzi
  • 167,459
  • 57
  • 363
  • 519
0

try sp_executesql

declare @f nvarchar(20)
declare @s nvarchar(20)
declare @sT nvarchar(100)

set @f = '20'
set @sT = 'set @s = ''' + @f + ''''

exec sp_executesql @sT, N'@s nvarchar(20) output', @s output

print @s
devio
  • 36,858
  • 7
  • 80
  • 143
  • I don't think this is what Markus wants: He wants to convert `'@f'` (note the quotes) to 20, with the string `'@f'` originating from a table. In your example, you use `@f` explicitly, i.e., it is bound at compile time. – Heinzi Mar 02 '10 at 14:29