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 @myVar2 = 'January 2, 2011'
SELECT * from myTable
myValTable contains columns myVariable and myValue:
myVar2||'January 2, 2011'
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?