CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012parameter-passinguser-defined-functions
Brian
Brian

Reputation: 1248

User-defined function with arbitrary parameter list

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.

Upvotes: 0

Views: 569

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

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.

Upvotes: 0

user1443098
user1443098

Reputation: 7645

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

Upvotes: 1

Related Questions