SELECT..CASE - Refactor T-SQL

Question

Hello

Can I refactor the below SQL CASE statements into single for each case ?

SELECT 
  CASE RDV.DOMAIN_CODE  WHEN 'L' THEN CN.FAMILY_NAME  ELSE NULL END AS [LEGAL_FAMILY_NAME],
  CASE RDV.DOMAIN_CODE  WHEN 'L' THEN CN.GIVEN_NAME  ELSE NULL END AS [LEGAL_GIVEN_NAME],
  CASE RDV.DOMAIN_CODE  WHEN 'L' THEN CN.MIDDLE_NAMES  ELSE NULL END AS [LEGAL_MIDDLE_NAMES],
  CASE RDV.DOMAIN_CODE  WHEN 'L' THEN CN.NAME_TITLE  ELSE NULL END AS [LEGAL_NAME_TITLE],

  CASE RDV.DOMAIN_CODE  WHEN 'P' THEN CN.FAMILY_NAME  ELSE NULL END AS [PREFERRED_FAMILY_NAME],
  CASE RDV.DOMAIN_CODE  WHEN 'P' THEN CN.GIVEN_NAME  ELSE NULL END AS [PREFERRED_GIVEN_NAME],
  CASE RDV.DOMAIN_CODE  WHEN 'P' THEN CN.MIDDLE_NAMES  ELSE NULL END AS [PREFERRED_MIDDLE_NAMES],
  CASE RDV.DOMAIN_CODE  WHEN 'P' THEN CN.NAME_TITLE  ELSE NULL END AS [PREFERRED_NAME_TITLE]

FROM dbo.CLIENT_NAME CN
JOIN dbo.REFERENCE_DOMAIN_VALUE RDV 
ON CN.NAME_TYPE_CODE = RDV.DOMAIN_CODE AND RDV.REFERENCE_DOMAIN_ID = '7966'

Answer 1

If RDV.DOMAIN_COD can only by 'P' or 'L' use NULLIf. It's cleaner.

NULLIF ( expression , expression )

NULLIF is equivalent to a searched CASE expression in which the two expressions are equal and the resulting expression is NULL.

SELECT
NullIf('P', RDV.DOMAIN_CODE) AS [LEGAL_FAMILY_NAME],
...
NullIf('L', RDV.DOMAIN_CODE) AS [PREFERRED_FAMILY_NAME],
...

Answer 2

No, you will require 8 separate statements as case and other such variants can only be used in a select to modify the results of a single column, not a series of columns.

Answer 3

Since a CASE expression returns a single value, you cannot take eight CASE expressions returning 8 values and make a single CASE expression that returns all eight.

Answer 4

A less efficient alternative with no cases:

SELECT LEGAL_FAMILY_NAME, LEGAL_GIVEN_NAME, LEGAL_MIDDLE_NAMES, LEGAL_NAME_TITLE,
       PREFERRED_FAMILY_NAME, PREFERRED_GIVEN_NAME, PREFERRED_MIDDLE_NAMES, PREFERRED_NAME_TITLE
FROM dbo.REFERENCE_DOMAIN_VALUE RDV
LEFT OUTER JOIN
  ( SELECT
      NAME_TYPE_CODE, 
      FAMILY_NAME AS [LEGAL_FAMILY_NAME],
      GIVEN_NAME AS [LEGAL_GIVEN_NAME],
      MIDDLE_NAMES AS [LEGAL_MIDDLE_NAMES],
      NAME_TITLE AS [LEGAL_NAME_TITLE]
    FROM dbo.CLIENT_NAME
    WHERE NAME_TYPE_CODE = 'L') LN ON RDV.DOMAIN_CODE = LN.NAME_TYPE_CODE
LEFT OUTER JOIN
  ( SELECT
      NAME_TYPE_CODE, 
      FAMILY_NAME AS [PREFERRED_FAMILY_NAME],
      GIVEN_NAME  AS [PREFERRED_GIVEN_NAME],
      MIDDLE_NAMES AS [PREFERRED_MIDDLE_NAMES],
      NAME_TITLE AS [PREFERRED_NAME_TITLE]
    FROM dbo.CLIENT_NAME
    WHERE NAME_TYPE_CODE = 'P') PN ON RDV.DOMAIN_CODE = PN.NAME_TYPE_CODE
  WHERE RDV.REFERENCE_DOMAIN_ID = '7966'

You could also use a temp table or table variable with all 8 columns and then do two inserts. You could also use a UNION ALL. My guess is that the 8 case statements are the most efficient way. This is especially true if you have some key where you will want some type of ClientID, Legal Names, Preferred Names so you will wrap a MAX around the cases or something and group by a ClientID......

You could generate the SQL script using syscols/INFORMATION_SCHEMA.columns with two case whens for each column 'CASE WHEN DOMAIN_CODE = ''P'' THEN ' + COLUMN_NAME + ' ELSE NULL END AS PREFERRED_' + COLUMN_NAME and then another for L. You could make a LOOP so that the same code executes once for P and once for L and get it down to one loop. Then you could EXEC the string directly, or PRINT it and put it into your SQL script. Anyway for just 8 columns I would cut/paste the case statements...

But anyway in general T-SQL is limited on being able to do for eaches over columns. Either you generate the script using a code generator (done in t-sql or another programming language) or you rethink your problem in another way. But many times you get better performance from duplicate cut/paste code. And many times it isn't worth the hassle of writing an external code generator (ie just for 8 case statements).