SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to use values keyword as parameter of a function


How to use values keyword as parameter of a function

Author
Message
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7833 Visits: 7145
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85346 Visits: 41078
Sorry.... forum had a long delay and I doubled up on a post. I removed the dupe here...

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85346 Visits: 41078
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/

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


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85346 Visits: 41078
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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1331 Visits: 1789
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 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
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7833 Visits: 7145
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85346 Visits: 41078
ScottPletcher (11/30/2012)
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?


Correct but the min/max still turns out slower. I'll try to remember to post my test code when I get home from work.

I also have an idea for solving the "base" problem here that might make both solutions seem slow.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search