create type GenericTable as table (D sql_variant)gocreate function AreEqual (@t as GenericTable readonly) returns bitasbegin declare @distinctRows int select @distinctRows = count(distinct D) from @t if (@distinctRows = 1) return 1 return 0end

declare @t GenericTableinsert into @t (D)values ('a'),('a'),('b')select dbo.AreEqual(@t) -- 0godeclare @t GenericTableinsert into @t (D)values ('a'),('a'),('a')select dbo.AreEqual(@t) -- 1godeclare @t GenericTableinsert into @t (D)values (1), (1), (2)select dbo.AreEqual(@t) -- 0godeclare @t GenericTableinsert into @t (D)values (1), (1), (1)select dbo.AreEqual(@t) -- 1

with temp as ( select 1 I, 1 D1, 1 D2, 1 D3 union select 2 I, 1 D1, 1 D2, 2 D3 union select 3 I, 1 D1, 2 D2, 3 D3)select I, dbo.AreEqual(values (D1),(D2),(D3))from temp

declare @t table (Id int, I1 int, I2 int, I3 int, I4 int, I5 int)-- insert ...select Id, dbo.AreEqual(I1, I3, I5), dbo.AreEqual(I1, I2), dbo.AreEqual(I4, I5)from @t-- orselect *from @twhere dbo.AreEqual(I1, I2) and dbo.AreEqual(I4, I5)

CREATE FUNCTION Equal ( @p1 INT = NULL, @p2 INT = NULL, @p3 INT = NULL, @p4 INT = NULL, @p5 INT = NULL )RETURNS VARCHAR(5)ASBEGIN 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

... dbo.Equal(1, 1, default, default, default, default, default, ...)

USE tempdb;GOCREATE FUNCTION Equal ( @p1 INT = NULL, @p2 INT = NULL, @p3 INT = NULL, @p4 INT = NULL, @p5 INT = NULL )RETURNS VARCHAR(5)ASBEGIN 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;GOIF object_id('tempdb..#testEnvironment') IS NOT NULLBEGIN DROP TABLE #testEnvironment;END; --5 Random rows of dataSELECT TOP 5 IDENTITY(INT,1,1) AS ID, (ABS(CHECKSUM(NEWID())) % 5) + 1 AS randomSmallInt1,(ABS(CHECKSUM(NEWID())) % 5) + 1 AS randomSmallInt2INTO #testEnvironmentFROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;GOSELECT ID, randomSmallInt1, randomSmallInt2, dbo.Equal(randomSmallInt1, randomSmallInt2)FROM #testEnvironment;

Msg 313, Level 16, State 2, Line 1An insufficient number of arguments were supplied for the procedure or function dbo.Equal.

IF object_id('tempdb..Equal') IS NOT NULLBEGIN DROP FUNCTION Equal;END;GOCREATE FUNCTION Equal ( @XML XML )RETURNS VARCHAR(5) ASBEGIN DECLARE @Return VARCHAR(5); SELECT @Return = CASE WHEN MIN(parm) = MAX(parm) THEN 'True' ELSE 'False' END FROM (SELECT [param] FROM (SELECT Tbl.Col.value('p1[1]', 'VARCHAR(MAX)'), Tbl.Col.value('p2[1]', 'VARCHAR(MAX)'), Tbl.Col.value('p3[1]', 'VARCHAR(MAX)'), Tbl.Col.value('p4[1]', 'VARCHAR(MAX)'), Tbl.Col.value('p5[1]', 'VARCHAR(MAX)'), Tbl.Col.value('p6[1]', 'VARCHAR(MAX)') FROM @XML.nodes('//row') Tbl(Col) )a(p1,p2,p3,p4,p5,p6) UNPIVOT ([param] FOR value IN (p1,p2,p3,p4,p5,p6))up ) AS X2(parm); RETURN @Return;END

IF object_id('tempdb..#testEnvironment') IS NOT NULLBEGIN DROP TABLE #testEnvironment;END; --100 Random rows of dataSELECT TOP 100 IDENTITY(INT,1,1) AS ID, (ABS(CHECKSUM(NEWID())) % 5) + 1 AS randomSmallInt1,(ABS(CHECKSUM(NEWID())) % 5) + 1 AS randomSmallInt2INTO #testEnvironmentFROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;GOSELECT ID, randomSmallInt1, randomSmallInt2, dbo.Equal(val)FROM #testEnvironmentCROSS APPLY (SELECT (SELECT randomSmallInt1 AS [p1], randomSmallInt2 AS [p2] FOR XML PATH('row'),TYPE))a(val);

create function EqualI2(@i1 int, @i2 int)...create function EqualI3(@i1 int, @i2 int, @i3 int)......create function EqualS2(@s1 varchar(max), @s2 varchar(max)...create function EqualS3(@s1 varchar(max), @s2 varchar(max), @s3 varchar(max))......create function EqualB2(@b1 bit, @b2 bit)...create function EqualB3(@b1 bit, @b2 bit, @b3 bit,...)......

GOif object_id('dbo.MyTestFunction') is not null drop function dbo.MyTestFunctionGO Create function MyTestFunction(@pString as varchar(8000),@pDelimiter as varchar(5)) returns bitasbegindeclare @Count as int; WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ),cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) SELECT 1 UNION ALL SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,len(@pDelimiter)) = @pDelimiter ),cteLen(N1,L1) AS(--==== Return start and length (for use in substring) SELECT s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000) FROM cteStart s )--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.select @Count = count(*) from ( SELECT SUBSTRING(@pString, l.N1, l.L1) Vals FROM cteLen l group by SUBSTRING(@pString, l.N1, l.L1) ) tif (@Count > 1) Return 1Return 0endGO--------------------Testing the Function---------------------------set nocount on;declare @Table as table (F1 varchar(10), F2 varchar(10), F3 varchar(255), F4 varchar(10))insert into @Tablevalues ('a','a','b','b') ,('a','a','a','a') insert into @Table values(1, 1, 2,3) ,(1, 1, 1,1) insert into @Table values('c','c','c','c') ,('d','d','c','c') declare @pString as varchar(max) declare @pDelimiter as varchar(2) = '|' select F1, F2, F3, F4, dbo.MyTestFunction( F1 + '|' + F2 + '|' + F3 + '|' + F4, @pDelimiter) from @Table select F1, F2, F3, dbo.MyTestFunction(F1 + '|' + F2 + '|' + F3 , @pDelimiter) from @Table select F1, F2, dbo.MyTestFunction( F3 + '|' + F4, @pDelimiter) from @Table

IF OBJECT_ID('tempdb..#Tmp') IS NOT NULL DROP TABLE #Tmp--The temp table makes things a little easierSELECT * INTO #Tmp FROM (select 1 I, '1' D1, '1' D2, '1' D3 union select 2 I, '1' D1, '1' D2, '2' D3 union select 3 I, '1' D1, '2' D2, '3' D3 union SELECT 4,'A','A','A' UNION SELECT 5,'B','A','A')X DECLARE @T AS GenericTable--Normalize the data so it plays nicely with our iTVFINSERT INTO @TSELECT I, Value FROM #Tmp CROSS APPLY (VALUES (D1),(D2),(D3))X(Value) SELECT * FROM AreEqual (@T) a INNER JOIN #Tmp b ON a.ID = b.I