November 28, 2012 at 6:28 am
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.
This is what I came up with:
create type GenericTable as table (D sql_variant)
go
create function AreEqual (@t as GenericTable readonly)
returns bit
as
begin
declare @distinctRows int
select @distinctRows = count(distinct D)
from @t
if (@distinctRows = 1) return 1
return 0
end
This works:
declare @t GenericTable
insert into @t (D)
values ('a'),('a'),('b')
select dbo.AreEqual(@t)-- 0
go
declare @t GenericTable
insert into @t (D)
values ('a'),('a'),('a')
select dbo.AreEqual(@t)-- 1
go
declare @t GenericTable
insert into @t (D)
values (1), (1), (2)
select dbo.AreEqual(@t) -- 0
go
declare @t GenericTable
insert into @t (D)
values (1), (1), (1)
select dbo.AreEqual(@t) -- 1
Is it possible to use 'values' statement directly as the parameter in the call of my AreEqual function? Something like this would be perfect:
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
November 28, 2012 at 7:23 am
Yes, with stored procedures this is easily achievable, but I cannot use it in scenarios like:
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
-- or
select *
from @t
where dbo.AreEqual(I1, I2) and dbo.AreEqual(I4, I5)
November 28, 2012 at 7:32 am
_simon_ (11/28/2012)
Yes, with stored procedures this is easily achievable, but I cannot use it in scenarios like:
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
-- or
select *
from @t
where dbo.AreEqual(I1, I2) and dbo.AreEqual(I4, I5)
Sure you can. Did you try turning Joe's code into a function?
e.g.
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
November 28, 2012 at 7:41 am
@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?
November 28, 2012 at 7:54 am
_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.
November 28, 2012 at 8:35 am
OK, this is a skeleton and would need editing to include however many params that you want to go in there.
IF object_id('tempdb..Equal') IS NOT NULL
BEGIN
DROP FUNCTION Equal;
END;
GO
CREATE FUNCTION Equal (
@XML XML
)
RETURNS VARCHAR(5) AS
BEGIN
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 we then take a quick look at the usage with some sample data: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
--100 Random rows of data
SELECT TOP 100 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(val)
FROM #testEnvironment
CROSS APPLY (SELECT (SELECT randomSmallInt1 AS [p1], randomSmallInt2 AS [p2] FOR XML PATH('row'),TYPE))a(val);
November 29, 2012 at 12:38 am
Seems legit, thanks 🙂 But the syntax, man... I'll probably just write my own set of methods...
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,...)...
...
November 29, 2012 at 12:23 pm
This would be so much easier in VB or C#...
Well it's not pretty and not sure if the parameter you pass in is going to be pretty but here goes...
I used Jeff Moden's Tally Splitter code so thanks to Jeff for his many contributions....
no guarantees on performance... 🙂
Not really an unlimited number of parameters... actually only 1 real parameter...
basic idea is to concatenate all the "parameter" values into a single delimited string and then parse and compare inside the function...
kinda works like a multi-parameter function.... =P
The delimiter can be up to 5 characters but you can change that easy enough... maybe use ::::: as the delimiter
GO
if object_id('dbo.MyTestFunction') is not null
drop function dbo.MyTestFunction
GO
Create function MyTestFunction(@pString as varchar(8000),@pDelimiter as varchar(5))
returns bit
as
begin
declare @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)
) t
if (@Count > 1) Return 1
Return 0
end
GO
--------------------Testing the Function---------------------------
set nocount on;
declare @Table as table (F1 varchar(10), F2 varchar(10), F3 varchar(255), F4 varchar(10))
insert into @Table
values ('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
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
November 29, 2012 at 2:19 pm
I have a slight rewrite that works. might not be better than any thing else but will eat as many sets with as many items in the set as you want to feed it(Of course you will need to modify the cross apply to include more columns).
IF OBJECT_ID('tempdb..#Tmp') IS NOT NULL DROP TABLE #Tmp
--The temp table makes things a little easier
SELECT * 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 iTVF
INSERT INTO @T
SELECT 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
It is still a 2 step process but now we can load as many sets as we want into the table valued parameter and get our proper results.
EDIT: This is also for me to play with a couple of things and get more experience with them. so it may not be fast or work in your situation but does use some things i have been studying.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 29, 2012 at 4:02 pm
FYI, I think you can touch-up the original function to get rid of the variable and the COUNT(DISTINCT):
alter function AreEqual (@t as GenericTable readonly)
returns bit
as
begin
return (
select case when min(D) = max(D) then 1 else 0 end
from @t
)
end
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 29, 2012 at 11:22 pm
Sorry.... forum had a long delay and I doubled up on a post. I removed the dupe here...
--Jeff Moden
Change is inevitable... Change for the better is not.
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)
AS
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/
That's ok but it's RBAR. Do it for the cte in the original post.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 29, 2012 at 11:26 pm
capnhector (11/29/2012)
EDIT: This is also for me to play with a couple of things and get more experience with them. so it may not be fast or work in your situation but does use some things i have been studying.
No.... you're absolutely on the correct path. The MIN=MAX method the others used is twice as slow as the original function. You can make it a bit faster still by turning it into an iTVF instead of a scalar function. And lose the join. If you rework the GenericTable TYPE just a bit, you won't need it. Give it a shot. You've got this, Cap'n!
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2012 at 1:42 pm
Jeff Moden (11/29/2012)
capnhector (11/29/2012)
EDIT: This is also for me to play with a couple of things and get more experience with them. so it may not be fast or work in your situation but does use some things i have been studying.No.... you're absolutely on the correct path. The MIN=MAX method the others used is twice as slow as the original function. You can make it a bit faster still by turning it into an iTVF instead of a scalar function. And lose the join. If you rework the GenericTable TYPE just a bit, you won't need it. Give it a shot. You've got this, Cap'n!
I cant believe i forgot to post the iTVF and new GenericTable rework as well. the things i was playing with was the cross apply values to unpivot the data.
The join was just to show that it came out with the right results. having the Generic Table type with just ID and Item allows any number of items to be passed in by just changing the cross apply unpivot.
CREATE TYPE GenericTable AS TABLE (ID INT, D sql_variant)
GO
CREATE FUNCTION AreEqual (@t as dbo.GenericTable readonly)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN SELECT ID, CASE WHEN COUNT(DISTINCT D) = 1 THEN 1 ELSE 0 END AS 'TF'
FROM @t
GROUP BY ID
GO
It is slow as hell for any thing larger than about 50,000 records on my system but chuggs them out just fine.
Here is the test bed i used
IF OBJECT_ID('tempdb..#Tmp') IS NOT NULL DROP TABLE #Tmp
--The temp table makes things a little easier
SELECT TOP (10000) --10 thousand is still nice and fast can change it later
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) I,
ABS(CHECKSUM(NEWID())) % 2 D1, --Gives us a small sub set so we will get more positive returns than a random seed of higher values
ABS(CHECKSUM(NEWID())) % 2 D2,
ABS(CHECKSUM(NEWID())) % 2 D3
INTO #Tmp
FROM sys.all_columns a, sys.all_columns b
DECLARE @T AS GenericTable
--Normalize the data so it plays nicely with our iTVF
INSERT INTO @T
SELECT 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
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 30, 2012 at 1:53 pm
Jeff Moden (11/29/2012)
capnhector (11/29/2012)
EDIT: This is also for me to play with a couple of things and get more experience with them. so it may not be fast or work in your situation but does use some things i have been studying.No.... you're absolutely on the correct path. The MIN=MAX method the others used is twice as slow as the original function. You can make it a bit faster still by turning it into an iTVF instead of a scalar function. And lose the join. If you rework the GenericTable TYPE just a bit, you won't need it. Give it a shot. You've got this, Cap'n!
Really??
Doesn't the DISTINCT require an (expensive) sort but min/max don't?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply