Tsql. Assign variable values from table

Hec Ramsey
Hec Ramsey used Ask the Experts™
on
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!!!
 
 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vikas GargAssociate Principal Engineer
Top Expert 2014

Commented:
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
Senior .Net Developer
Commented:
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

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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?
Ensure you’re charging the right price for your IT

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

Author

Commented:
Scott the table is described in the question ( myValTable).  The variables are known to the proc.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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

Author

Commented:
I think pivot table is the way to go
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial