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!!!
 
 
Hec RamseyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
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
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Are you looking for different result sets (eg: a cursor or a loop) or are you trying to do an in?

if you're looking for different result sets:

select * 
into #tmp 
from myTable

while (select count(*) from #tmp) > 0
begin
          select top 1 @MyVar1= Var1, @MyVar2 = Var2 from #tmp

          -- select

         delete from #tmp where Var1 = @myvar1 and var2 = @myVar2
end

drop table #tmp

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
Hec RamseyAuthor Commented:
Scott the table is described in the question ( myValTable).  The variables are known to the proc.
0
Scott PletcherSenior DBACommented:
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
0
Hec RamseyAuthor Commented:
I think pivot table is the way to go
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Hec Ramsey, do you still need help with this question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.