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!
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!!!
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!!!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
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:
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
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?
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?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial