[SOLUTION] Tsql. Assign variable values from table

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!!!

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

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.

LVL 15

Vikas GargAssociate Principal EngineerCommented: 2015-08-13

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

LVL 42

Kyle AbrahamsSenior .Net DeveloperCommented: 2015-08-13

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:

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

LVL 72

Scott PletcherSenior DBACommented: 2015-08-14

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 RamseyAuthor Commented: 2015-08-14

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

LVL 55

Vitor MontalvãoMSSQL Senior EngineerCommented: 2015-08-18

Hec Ramsey, do you still need help with this question?

Tsql. Assign variable values from table

Who is Participating?