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:
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?
Hec RamseyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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 GargAssociate Principal EngineerCommented:

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
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
          select top 1 @MyVar1= Var1, @MyVar2 = Var2 from #tmp

          -- select

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

drop table #tmp

Open in new window

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?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Hec RamseyAuthor Commented:
Scott the table is described in the question ( myValTable).  The variables are known to the proc.
Scott PletcherSenior DBACommented:
    @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 RamseyAuthor Commented:
I think pivot table is the way to go
Vitor MontalvãoMSSQL Senior EngineerCommented:
Hec Ramsey, do you still need help with this question?
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.