Sean Ogden Sean Ogden - 4 months ago 9
SQL Question

SQL Server UDF array inputs and outputs

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

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
;
Comments