How do I write an any-number-of-params function in SQL Server

Question

Coalesce seems to work with any number of parameters and return the first one that is not null. How can I write a function like that? One that does not have a fixed number of parameters?

An example of the usage of a function fMax:

select Length = dbo.fMax(box.Height, box.Width, box.Depth)
from dbo.tBox box
where box.ShipmentId = 1234

With such a function I would not have to write something like this:

select Length = (
   select MAX(side)
   from (values (box.Height), (box.Width), (box.Depth)) as sides(side))
from dbo.tBox box
where box.ShipmentId = 1234

You can't write one in T-SQL. I think you can accept an unknown number of parameters if you write an extended stored procedure (in e.g. C), but support for that feature is going away in the future, and they're not the same as functions (they cannot be composed into a larger query) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Sep 24, 2013 at 10:21
can you show a sample of what you are trying to achieve as coalesce should work as far as what i can read from your question? — Tanner
– Tanner, Commented Sep 24, 2013 at 10:21

Hamlet Hakobyan · Accepted Answer · 2013-09-24 10:27:16Z

2

If you use SQL Server 2008 and above you can use the Table-Valued Parameters.

answered Sep 24, 2013 at 10:27

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

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

Comments

DrCopyPaste · Accepted Answer · 2013-09-24 11:07:51Z

0

Unfortunately, after trying that out, I am pretty sure that you cannot write COALESCE-like functions yourself in T-SQL.

I was pretty sure that you can use socalled CLR-Functions that you can code for example in C# to make up for some lacking features in SQL-Server. However I have to agree with the comment below, that this does not free you from the need to provide a parameter list in sql to introduce the new function in which you still would have that restriction.

So in short, you cannot code such functions yourself for T-SQL.

edited Sep 24, 2013 at 11:07

answered Sep 24, 2013 at 10:27

DrCopyPaste

4,1552 gold badges24 silver badges58 bronze badges

2 Comments

Damien_The_Unbeliever Over a year ago

But you cannot combine those two concepts since when you import a CLR UDF you have to provide a parameter list.

DrCopyPaste Over a year ago

Oh darn, really? I was just about to try that out myself, well thats a downer. I'll just finish my try and then come back and edit/delete this, so it seems there is no possibility to achieve what the OP wants after all ;/

Fergus Bown · Accepted Answer · 2013-10-03 15:03:43Z

0

As the other respondants say, I don't think you can do exactly what you ask for.

However I think you could make a reasonable approximation using default parameters. If you know a reasonable upper limit, the you could define a function something like this:

--Edit

Turns out you can't have default values on a UDF. Or rather you can define them, but you still have to specify the values when calling the function

That means the best you can do is to have function with the maximum number of parameters:

CREATE FUNCTION dbo.fMax
(
    @Value1 float,
    @Value2 float,
    @Value3 float,
    @Value4 float
)
RETURNS float
AS
BEGIN
  DECLARE @Result float

  SELECT @Value1 = COALESCE(@Value1, @Value2, @Value3, @Value4)
  SELECT @Value2 = COALESCE(@Value2, @Value1, @Value3, @Value4)
  SELECT @Value3 = COALESCE(@Value3, @Value1, @Value2, @Value4)
  SELECT @Value4 = COALESCE(@Value4, @Value1, @Value2, @Value3)

  SELECT @Result = @Value1

  IF (@Value2 > @Result)
    SELECT @Result = @Value2

  IF (@Value3 > @Result)
    SELECT @Result = @Value3

  IF (@Value4 > @Result)
    SELECT @Result = @Value4

  RETURN @Result
END

and call it like this:

SELECT dbo.fMax(1, 5, 4, null)

edited Oct 3, 2013 at 15:03

answered Sep 24, 2013 at 14:28

Fergus Bown

1,6969 silver badges16 bronze badges

2 Comments

Kristoffer Over a year ago

This approach looked promising, but SQL Server Management Studio complains if I only give it three params, even though @Value4 has a null default.

Fergus Bown Over a year ago

See edit - turns out optional values are ignored in UDFs... :(

Collectives™ on Stack Overflow

How do I write an any-number-of-params function in SQL Server

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related