Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How to use values keyword as parameter of a function Expand / Collapse
Author
Message
Posted Wednesday, November 28, 2012 6:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:55 PM
Points: 156, Visits: 662
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

Post #1389703
Posted Wednesday, November 28, 2012 7:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:46 PM
Points: 1,945, Visits: 3,125
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/

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


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1389763
Posted Wednesday, November 28, 2012 7:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:55 PM
Points: 156, Visits: 662
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)

Post #1389770
Posted Wednesday, November 28, 2012 7:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:28 PM
Points: 2,386, Visits: 7,611
_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




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1389774
Posted Wednesday, November 28, 2012 7:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:55 PM
Points: 156, Visits: 662
@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?
Post #1389783
Posted Wednesday, November 28, 2012 7:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:28 PM
Points: 2,386, Visits: 7,611
_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.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1389798
Posted Wednesday, November 28, 2012 8:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:28 PM
Points: 2,386, Visits: 7,611
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);




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1389841
Posted Thursday, November 29, 2012 12:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:55 PM
Points: 156, Visits: 662
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,...)...
...

Post #1390319
Posted Thursday, November 29, 2012 12:23 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504
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 - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1390774
Posted Thursday, November 29, 2012 2:19 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1390859
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse