August 2, 2010 at 3:42 am
Hi Folks,
Is there a way of choosing a specific column of a table in a UDF, given that you can't use dynamic SQL?
Issue is this, we have a lookup table:
CREATE TABLE Stuff(Age INT, Sex VARCHAR(1), X as Money,Y as Money,Z as Money)
Depending on the age/sex columns x through z have different numeric values, which are used in a calculation that the UDF then returns.
Is there a way of doing this (pseudo sql)
DECLARE @Var varchar(2)
SELECT @Var=CASE WHEN @val>20 THEN 'X' WHEN @val>10 THEN 'Y' ELSE 'Z' END
SELECT [@var] FROM Stuff WHERE Age=23 and Sex='M'
Limitations are that we cannot flip the table design on its head with a key of Age, Sex, Value, Result
due to restrictions on the db. (Doesn't everyone say this!) and as its version independant it can't be a CLR. Any help appreciated, even if its no can do.
August 2, 2010 at 7:45 am
Hi Folks,
Solution was staring me in the face all along (CASE). So I now do this:
SELECT CASE @val WHEN 1 THEN X
WHEN 2 THEN Y
ELSE Z END
There are only a fixed number of fields, but its still a little messy. If anyone can think of better way then let me know.
Effectively @val is a calculated field that returns a numeric value to cross reference in the table - there are 20 plus fields, each returning a different rate.
Kind Regards
Jamie
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply