## How to use values keyword as parameter of a function

 Author Message ScottPletcher SSC-Forever Group: General Forum Members Points: 46170 Visits: 8026 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 bitasbeginreturn ( select case when min(D) = max(D) then 1 else 0 end from @t)end` SQL DBA,SQL Server MVP(07, 08, 09) 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. Jeff Moden SSC Guru Group: General Forum Members Points: 504455 Visits: 44233 Sorry.... forum had a long delay and I doubled up on a post. I removed the dupe here... --Jeff ModenRBAR 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 problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 504455 Visits: 44233 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)ASSELECT 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 ModenRBAR 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 problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 504455 Visits: 44233 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 ModenRBAR 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 problemsHow to post performance problemsForum FAQs CapnHector SSCrazy Eights Group: General Forum Members Points: 8101 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)GOCREATE FUNCTION AreEqual (@t as dbo.GenericTable readonly) RETURNS TABLE WITH SCHEMABINDINGASRETURN SELECT ID, CASE WHEN COUNT(DISTINCT D) = 1 THEN 1 ELSE 0 END AS 'TF' FROM @t GROUP BY IDGO`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 easierSELECT 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 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` 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 ShawNeed to Split some strings? Jeff Moden's DelimitedSplit8KJeff Moden's Cross tab and Pivots Part 1Jeff Moden's Cross tab and Pivots Part 2 ScottPletcher SSC-Forever Group: General Forum Members Points: 46170 Visits: 8026 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) 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. Jeff Moden SSC Guru Group: General Forum Members Points: 504455 Visits: 44233 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 ModenRBAR 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 problemsHow to post performance problemsForum FAQs