Avatar of Hec Ramsey
Hec Ramsey
Flag 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!!!
 
 
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Vikas Garg

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
Kyle Abrahams

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Scott Pletcher

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?
Hec Ramsey

ASKER
Scott the table is described in the question ( myValTable).  The variables are known to the proc.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Scott Pletcher

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
Hec Ramsey

ASKER
I think pivot table is the way to go
Vitor Montalvão

Hec Ramsey, do you still need help with this question?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.