Link to home
Create AccountLog in
Avatar of Hec Ramsey
Hec RamseyFlag for United States of America

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!!!
 
 
Avatar of Vikas Garg
Vikas Garg
Flag of India image

HI,

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
ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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?
Avatar of Hec Ramsey

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
I think pivot table is the way to go
Hec Ramsey, do you still need help with this question?