SQL Server Performance Forum – Threads Archive

Alot of user parameters into variables?

Hello! I have a table that stores alot of user parameters, now I need to put all those values into variables. like this. select @parameter1 = parameterValue from mytable where userId = 123 and parameterId = 1
select @parameter2 = parameterValue from mytable where userId = 123 and parameterId = 2
.
select @parameter100 = parameterValue from mytable where userId = 123 and parameterId = 100 I will then use my the variables to query against a table, I dont want to do a subquery each time, that is the reason I want to have them stored inside a variable. The problem here is that I have to execute it 100 times, I would like to do something like this. select case
when parameterId = 1 then @parameter1 = parametervalue
when parameterId = 2 then @parameter2 = parametervalue
.
when parameterId = 100 then @parameter100 = parametervalue
end
from mytable where userId = 123 Is this possible to do? Or do you have some other suggestion?

---

This is one way:
select
@parameter1 = sum(case when parameterId = 1 then parameterValue end),
@parameter2 = sum(case when parameterId = 2 then parameterValue end)
from mytable where userId = 123
So the question left is if there is another better way to do this. Instead of declaring around 100 variables?

---

create a function
create function GetParam
(
@UserID int ,
@ParmID int
)
retutns varchar(100)
as
begin
declare @s varchar(100)
select @s = parameterValue from mytable where userId = @UserID and parameterId = @ParmID
return @s
end
go then select @parameter1 = dbo.GetParam(123,1)
@parameter2 = dbo.GetParam(123,2)
…

---

will only work for numeric values – I would expect this to be character which might contain numeric values or not.
could change the sum to a max to get that to work.

---

use sql_variant data type instead of varchar.
do you get one parameter or multiple parameter? when you say "I will then use my the variables to query against a table, I dont want to do a subquery each time", how’s the query formatted? try running the query using GetParam function above, like SELECT *
FROM TABLE1
WHERE
ID = GetParam(@UserID, @ParamID) –if you want to set the paramid somewhere else
OR ID = COALESCE(GetParam(@UserID, 2), GetParam(@UserID, 7)) or SELECT *
FROM TABLE1
WHERE
Param1 = GetParam(@UserID,1) –if you know this will always be parameter 1
and Param5 = GetParam(@UserID,5)
May the Almighty God bless us all!
www.empoweredinformation.com

---

]]>