Select multiple columns as a result of a case expression

Question

This is what I would like to do:

case p.residency_status
    when 0 then
        dbo.fn_formatAddress(r1.prm_name, p.py_hospital, p.py_address1, p.py_city, p.pyear_state)
    when 1 then
        dbo.fn_formatAddress(r1.prm_name, p.respgm_hospital, p.respgm_address1, p.respgm_city, p.respgm_state)
    when 2 then
        dbo.fn_formatAddress(r1.prm_name, p.curr_hospital, p.curr_address1, curr.city, p.curr_state)
end

Is there any way to do this? Essentially, based on residency_status, I will need to pass different parameters to a function.

Answer 1

Yes, but you can't use the case structure in T-SQL the way you are probably used to using select case as a control structure. The syntax in this case (heh) would be more like the following.

Select
case 
    when p.residency_status = 0 then
        dbo.fn_formatAddress(r1.prm_name, p.py_hospital, p.py_address1, p.py_city, p.pyear_state)
    when p.residency_status = 1 then
        dbo.fn_formatAddress(r1.prm_name, p.respgm_hospital, p.respgm_address1, p.respgm_city, p.respgm_state)
    when p.residency_status = 2 then
        dbo.fn_formatAddress(r1.prm_name, p.curr_hospital, p.curr_address1, curr.city, p.curr_state)
end
from
table p

Answer 2

If you need the individual fields outside of the function, too, you can do sub-select like this:

SELECT
  *,
  dbo.fn_formatAddress(
    prm_name, 
    resident_hospital, 
    resident_address1, 
    resident_city,
    resident_state
  ) FormattedAddress
FROM
  (
    SELECT
      r1.prm_name,
      ...,
      CASE p.residency_status 
        WHEN 0 THEN p.py_hospital
        WHEN 1 THEN p.respgm_hospital
        WHEN 2 THEN p.curr_hospital
      END resident_hospital,
      CASE p.residency_status 
        WHEN 0 THEN p.py_address1
        WHEN 1 THEN p.respgm_address1
        WHEN 2 THEN p.curr_address1
      END resident_address1,
      CASE p.residency_status 
        WHEN 0 THEN p.py_city
        WHEN 1 THEN p.respgm_city
        WHEN 2 THEN p.curr_city
      END resident_city,
      CASE p.residency_status 
        WHEN 0 THEN p.pyear_state
        WHEN 1 THEN p.respgm_state
        WHEN 2 THEN p.curr_state
      END resident_city
    FROM
      r1 INNER JOIN p ON p.someField = r1.someField
    WHERE
      someCondition = 1
  ) AS InnerQuery