SQL Server UDF array inputs and outputs

Question

I have a set of columns CODE_1-10, which contain diagnostic codes. I want to create a set of variables CODE_GROUP_1-17, which indicate whether or not one of some particular set of diagnostic codes matches any of the CODE_1-10 variables. For example, CODE_GROUP_1 = 1 if any of CODE_1-10 match either '123' or '456', and CODE_GROUP_2 = 1 if any of CODE_1-10 match '789','111','333','444' or 'foo'.

Here's an example of how you could do this using values constructors.

    CASE WHEN (SELECT count(value.val)
       FROM    (VALUES (CODE_1)
                   ,   (CODE_2)
                   ,   (CODE_3)
                   ,   (CODE_4)
                   ,   (CODE_5)
                   ,   (CODE_6)
                   ,   (CODE_7)
                   ,   (CODE_8)
                   ,   (CODE_9)
                   ,   (CODE_10)
               ) AS value(val)
     WHERE value.val in ('123', '456')
   ) > 0 THEN 1 ELSE 0 END AS CODE_GROUP_1,

   CASE WHEN (SELECT count(value.val)
       FROM    (VALUES (CODE_1)
                   ,   (CODE_2)
                   ,   (CODE_3)
                   ,   (CODE_4)
                   ,   (CODE_5)
                   ,   (CODE_6)
                   ,   (CODE_7)
                   ,   (CODE_8)
                   ,   (CODE_9)
                   ,   (CODE_10)
               ) AS value(val)
     WHERE value.val in ('789','111','333','444','foo')
   ) > 0 THEN 1 ELSE 0 END AS CODE_GROUP_2

I am wondering if there is another way to do this that is more efficient. Is there a way to make a CLR UDF that takes an array of CODE_1-10, and outputs a set of columns CODE_GROUP_1-17?

Answer 1

You could at least avoid the repetition of FROM (VALUES ...) like this:

SELECT
  CODE_GROUP_1 = COUNT(DISTINCT CASE WHEN val IN ('123', '456')                  THEN 1 END),
  CODE_GROUP_2 = COUNT(DISTINCT CASE WHEN val IN ('789','111','333','444','foo') THEN 1 END),
  ...
FROM
  (
    VALUES
      (CODE_1),
      (CODE_2),
      (CODE_3),
      (CODE_4),
      (CODE_5),
      (CODE_6),
      (CODE_7),
      (CODE_8),
      (CODE_9),
      (CODE_10)
  ) AS value(val)

If CODE_1, CODE_2 etc. are column names, you can use the above query as a derived table in CROSS APPLY:

SELECT
  ...
FROM
  dbo.atable  -- table containing CODE_1, CODE_2 etc.
  CROSS APPLY
  (
    SELECT ...  -- the above query
  ) AS x
;