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.