Home Forums SQL Server 2008 T-SQL (SS2K8) How to use values keyword as parameter of a function RE: How to use values keyword as parameter of a function

  • 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[/url] 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[/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]