How to pass column name to a function?
-
Let's say a table has columns ID, Red, Yellow, and Green.
I want to be able to retrieve the value of either Red, Yellow, or Green by passing in that column name and a value for ID, as follows:
Set @val = fGetData(12345, 'Red')
or
Set @val = fGetData(1042, 'Yellow')
I do NOT want to have this language in my function:
IF @ColName = 'Red'
SELECT @val = Red FROM MyTable WHERE ID = @IDVal
IF @ColName = 'Yellow'
SELECT @val = Yellow FROM MyTable WHERE ID = @IDVal
In other words, I don't want to hard-code the column name, because I might add or delete columns and do not wish to rewrite the function.
It should work something like this:
SELECT @val = @ColName FROM MyTable.
Of course, that DOES NOT work because it would return 'Red' or whatever the column name is rather than the VALUE of that column and row.
I have looked at dynamic SQL, but you have to use EXEC, which does not work in a function. And in a stored proc, EXEC returns result set. If I knew how to get the single value of the result set and pass the value (not the result set) as an output param, that would suffice.
Thanks,
Scott
-
Stored procedures do not just return resultsets. Check BOL for info on OUTPUT parameters in stored procs.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Right. But the EXEC command on a ('SELECT...') statement does. So within the stored proc that runs the EXEC command, I would have to copy the resultset value into an output param.
But I suspect that there is a way using a function to do what I want to do. I prefer not to use a stored proc.
-
I don't think there is another way of doing this besides using a dynamic query. If you declare your sql string as nvarchar you can use sp_executesql and through parmeter definition get the output parameter back
DECLARE @strSQL nvarchar(1500), @ParmDefinition nvarchar(100)
SET @strSQL = 'SELECT @val = '+@ColName+' FROM MyTable'
SET @ParmDefinition = N'@Val int OUTPUT '
EXEC sp_executesql @strSQL, @ParmDefinition, @val OUTPUT
-
Create a table variable or a temp table and insert into it from the EXEC command. Once you have captured it in the table, you can save the value in the output parameter.
-
or make a stored function that returns the value you are interested in. If the values of the columns Red, Yellow, Green is an Int, this would do what you are after:
create function fGetData (
@ID as int,
@Color as varchar(10)
)
returns Int
as
begin
return (Select case @Color
when 'Green' then green
when 'Red' then red
when 'Yellow' then yellow
end
from mytable
where id = @id
)
end
then your code would look something like:
select fGetData(ID,Color) .....
or if you are already using MyTable in your query, you can just use the case statement to get the value you want.
-
Thanks, but your code does what I do NOT wish to do, namely, hardcode the names of the columns into the function.
Come on, SQL Pros! Surely one of you can figure this out!
Regards,
Scott
-
We don't normally do such things because it is not nice SQL... The proper design for you DB table would be
ID int
colour varchar(20)
val int
Then you can easily query the table by doing
select val
from MyTable
where ID = @ID and colour = @colour
You'll otherwise need to use dynamic SQL which as others have pointed out is messy and as you've found out, you cannot run in a UDF. This is because UDFs cannot have any side effects. I'm sorry, but that's probably the long and short of it - change your table design if you really want the flexibility you are after.
And, if you have other code dependant on that table, then create a view that the other code references that happens to have the same name as the table and rename the base table instead. The view can do something like
select ID, (select val from MyTable where ID = MT.ID and colour = 'red') as RED, (select val from MyTable where ID = MT.ID and colour = 'white') AS WHITE, .... from (select distinct ID from myTable) MT(Note that you could also try a groupBy on ID instead to remove the derived table, or do distinct on the entire line which is probably inefficient?).
Best of luck!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply