We help IT Professionals succeed at work.
Get Started

Tsql. Assign variable values from table

72 Views
Last Modified: 2015-09-02
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
Director of Information Technology
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE