I am trying to assign variable values in a proc from a table. I'm doing this so the values can be changed in prod without editing script, just inserting to the table. For purposes of this question I have left out details like versioning.
My problem is how to assign the value to to @variable at runtime
My code currently reads (EXAMPLE)
Dim @MyVar1 as varchar(100)
dim @myVar2 as datetime
Set @MyVar1='Frankenstein'
Set @myVar2 = 'January 2, 2011'
SELECT * from myTable
where Monster=@myVar1
and myDate=@myVar2
myValTable contains columns myVariable and myValue:
myVar1||'Frankenstein'
myVar2||'January 2, 2011'
myVar3||'Spark Plugs'
I can't assign the values using dynamic sql because that would be in a different scope than the rest of the proc. (correct?,I've tried it, no go)
I'm thinking pivot the table on col @myVariable then assign with a SELECT @myVar1 = myVar1
but that seems like a lot of code, since the number of variables is unknown.
Any cheaper alternatives?
Thanks!!!
You can do something like this
declare @out_var varchar(max);
execute sp_executesql
N'select @out_var = ''hi world''',
N'@out_var varchar(max) OUTPUT',
@out_var = @out_var output;
select @out_var;
for dynamic variable value allocation