选择多个列作为case表达式的结果

是的,但是不能像使用selectcase作为控制结构那样使用t-SQL中的case结构。本例中的语法(heh)更类似于以下内容。

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

如果您也需要函数外的单个字段,可以执行如下子选择:

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