User-defined function with arbitrary parameter list

Question

The Coalesce() built-in function accepts an arbitrary number of parameters.

Is it possible in SQL Server 2012 to write a user-defined function that does this, with the same syntax? I don't want to use a table variable or comma-delimited list.

I want to implement a Coalesce() function that returns the LOWEST non-null element that's passed in rather than the FIRST non-null element that's passed in. This is to meet a new user requirement. Coalesce() doesn't require creating a table variable and, since I'm replacing the existing Coalesce() calls with new logic, I want to write my new function to behave the same way, including accepting an arbitrary number of elements.

I understand that there are other approaches that I could take here - I want to minimize the amount of re-writing that I have to do.

Kind of what it was intended for, specifically table variable parameter. I'd stay away from comma-delimited lists since you just have to turn it into a table afterwards. But a table valued parameter is the best option here. However, this could be an XY Problem, could you tell us what you are trying to accomplish? — S3S
– S3S, Commented Jul 10, 2018 at 16:09
You can create a function with optional parameters and default values. (An example of an SP with optional parameters is here.) That isn't the same as having an unpredictable number of sql_variant parameters, but it may be useful. — HABO
– HABO, Commented Jul 10, 2018 at 17:16
In my case, "earliest date". But again, that's beside the point of the question, which is "can one write a UDF in SQL Server that accepts an arbitrary number of parameters, the way the Coalesce() function does?" — Brian
– Brian, Commented Jul 11, 2018 at 2:55

user1443098 · Accepted Answer · 2018-07-10 16:11:53Z

1

Yes, it is possible. just not in T-SQL. You need to write a CLR function

answered Jul 10, 2018 at 16:11

user1443098

7,75511 gold badges43 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brian Over a year ago

Actually, @ALollz, this answers my question exactly.

Lukasz Szozda · Accepted Answer · 2018-07-11 04:59:21Z

0

I don't want to use a table variable or comma-delimited list if I can avoid it.

TVP is the way to go. But if you don't want to define table type you could use old-fashioned XML:

CREATE TABLE tab(id INT, a INT, b INT, c INT, d INT);
INSERT INTO tab(id,a,b,c,d) VALUES(1,1,NULL,NULL,NULL);
INSERT INTO tab(id,a,b,c,d) VALUES(2,2,2,NULL,NULL);
INSERT INTO tab(id,a,b,c,d) VALUES(3,3,3,3,NULL);
INSERT INTO tab(id,a,b,c,d) VALUES(4,4,4,4,4);

Function:

CREATE FUNCTION dbo.my_func(@input XML)
RETURNS INT
AS
BEGIN
RETURN
(
  SELECT SUM(a.b.value('.', 'INT'))   --any logic here, for simplicity's sake SUM
  FROM @input.nodes('/*') AS sub(c)
  CROSS APPLY sub.c.nodes('@*') a(b)
);
END;

And call:

SELECT t1.*, sub.*, dbo.my_func(sub.x) AS result
FROM tab t1
CROSS APPLY (SELECT a,b,c,d
             FROM (SELECT 1) t2(q) FOR XML AUTO) sub(x);

DBFiddle Demo

Output:

┌────┬───┬──────┬──────┬──────┬───────────────────────────────┬────────┐
│ id │ a │  b   │  c   │  d   │               x               │ result │
├────┼───┼──────┼──────┼──────┼───────────────────────────────┼────────┤
│  1 │ 1 │ null │ null │ null │ <t2 a="1"/>                   │      1 │
│  2 │ 2 │ 2    │ null │ null │ <t2 a="2" b="2"/>             │      4 │
│  3 │ 3 │ 3    │ 3    │ null │ <t2 a="3" b="3" c="3"/>       │      9 │
│  4 │ 4 │ 4    │ 4    │ 4    │ <t2 a="4" b="4" c="4" d="4"/> │     16 │
└────┴───┴──────┴──────┴──────┴───────────────────────────────┴────────┘

EDIT:

No, it is not possible to define UDF with variable args like COLAESCE. The best you can get is:

LOWEST2(a1, a2)
LOWEST3(a1, a2, a3)
LOWEST4(a1, a2, a3, a4)
...
LOWESTn(a1, a2, a3, a4, ..., an)

Please note that function names are different. Plus you need to use data type that will satisfy most of your cases or even sqlvariant.

edited Jul 11, 2018 at 4:59

answered Jul 10, 2018 at 16:27

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

7 Comments

Brian Over a year ago

This is not a user-defined function that accepts an arbitrary number of parameters the way Coalesce() does.

Lukasz Szozda Over a year ago

@Brian No in pure T-SQL you can't do what you want. I answered your second part. Correct way is TVP.

Brian Over a year ago

No, you didn't, because passing in an XML string is not the "same syntax" as the Coalesce() function.

S3S Over a year ago

While your comments are true @Brian this is a native way without changing server defaults and introducing something as vast as CLR which wouldn't be as easy to implement, as portable, etc.

Brian Over a year ago

Perhaps, @scsimon, but that doesn't change the fact that Lukasz's reply did not meet my needs. I asked "Is it possible to do X? Here is why I want to do X", and this reply is a long-winded "Here's how to do Y, which is similar and is what I think you should do instead."

|

Collectives™ on Stack Overflow

User-defined function with arbitrary parameter list

2 Answers 2

1 Comment

EDIT:

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

EDIT:

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related