_simon_ (11/28/2012)
@Cadavre: The function should be able to compare multiple values (for example up to 20), then I would need to write a lot of 'default's to compare just 3 values for example:
... dbo.Equal(1, 1, default, default, default, default, default, ...)
Or am I missing something?
Apologies, I assumed that defaults would work the same. I've set up a sample script and so can now see your issue: -
USE tempdb;
GO
CREATE FUNCTION Equal (
@p1 INT = NULL,
@p2 INT = NULL,
@p3 INT = NULL,
@p4 INT = NULL,
@p5 INT = NULL
)
RETURNS VARCHAR(5)
AS
BEGIN
DECLARE @ReturnValue VARCHAR(5);
SELECT @ReturnValue = 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);
RETURN @ReturnValue;
END;
GO
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
--5 Random rows of data
SELECT TOP 5 IDENTITY(INT,1,1) AS ID,
(ABS(CHECKSUM(NEWID())) % 5) + 1 AS randomSmallInt1,
(ABS(CHECKSUM(NEWID())) % 5) + 1 AS randomSmallInt2
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
GO
SELECT ID, randomSmallInt1, randomSmallInt2, dbo.Equal(randomSmallInt1, randomSmallInt2)
FROM #testEnvironment;
Which results in: -
Msg 313, Level 16, State 2, Line 1
An insufficient number of arguments were supplied for the procedure or function dbo.Equal.