asked on Tsql. Assign variable values from table
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!!!
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!!!
Microsoft SQL ServerMicrosoft SQL Server 2008
Last Comment
Vitor Montalvão
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Not clear on what you're trying to do.
Does the table define the variable name(s) (?) or just the value(s)?
If the variable/value is completely unknown to the proc, how would it ever use it?
Does the table define the variable name(s) (?) or just the value(s)?
If the variable/value is completely unknown to the proc, how would it ever use it?
ASKER
Scott the table is described in the question ( myValTable). The variables are known to the proc.
SELECT
@MyVar1 = MAX(CASE WHEN myVariable = 'MyVar1' THEN MyValue END),
@MyVar2 = MAX(CASE WHEN myVariable = 'MyVar2' THEN MyValue END),
@MyVar3 = MAX(CASE WHEN myVariable = 'MyVar3' THEN MyValue END)
--,...
FROM myValTable
@MyVar1 = MAX(CASE WHEN myVariable = 'MyVar1' THEN MyValue END),
@MyVar2 = MAX(CASE WHEN myVariable = 'MyVar2' THEN MyValue END),
@MyVar3 = MAX(CASE WHEN myVariable = 'MyVar3' THEN MyValue END)
--,...
FROM myValTable
ASKER
I think pivot table is the way to go
Hec Ramsey, do you still need help with this question?
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