RE: How to use values keyword as parameter of a function

SSC Guru

Points: 1003907

November 29, 2012 at 11:26 pm

CELKO (11/28/2012)

I would like to have a function which accepts any number of parameters with the same type and returns true if they are all equal and false otherwise.

You have to have a fixed number of parameters, but it can be up to 2K of them. Here is a skeleton:

CREATE PROCEDURE Equal

(@p1 INTEGER = NULL,

@p2 INTEGER = NULL,

@p3 INTEGER = NULL,

@p4 INTEGER = NULL,

@p5 INTEGER = NULL)

SELECT CASE WHEN MIN(parm) = MAX(parm) THEN 'True' ELSE 'False' END

FROM (SELECT parm

FROM (VALUES (@p1), (@p2), (@p3), (@p4), (@p5)) AS X1(parm)

WHERE parm IS NOT NULL)

AS X2(parm);

EXEC Equal 12,12,12,12,12;

EXEC Equal 12,12,13;

EXEC Equal 12;

The use of the VALUES() constructor is new to SQL Server programmers, but other products have had it for awhile now.

I have a two articles on this topic at Simple Talk;

http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/

http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/%5B/quote%5D

That's ok but it's RBAR. Do it for the cte in the original post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)