How to pass column name to a function?

• Scott Pendleton

  Valued Member

  Points: 65

  More actions

  January 16, 2006 at 8:22 am

  #170165

  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

• Phil Parkin

  SSC Guru

  Points: 246990

  More actions

  January 16, 2006 at 9:02 am

  #614934

  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

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Scott Pendleton

  Valued Member

  Points: 65

  More actions

  January 16, 2006 at 9:10 am

  #614936

  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.

• latinsky

  SSC Veteran

  Points: 203

  More actions

  January 17, 2006 at 9:08 am

  #615191

  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

• David Korb

  SSCrazy

  Points: 2505

  More actions

  January 17, 2006 at 10:55 am

  #615224

  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.

• Bill Scott

  SSCrazy

  Points: 2338

  More actions

  January 17, 2006 at 11:31 am

  #615234

  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.

   

• Scott Pendleton

  Valued Member

  Points: 65

  More actions

  January 17, 2006 at 4:44 pm

  #615331

  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

• Ian Yates

  SSCoach

  Points: 19738

  More actions

  January 17, 2006 at 6:57 pm

  #615347

  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!