Passing a list of values to a Stored Procedure (Part II)

SELECT ... WHERE CustomerID IN @List

[blue]SELECT[/blue] [gray]...[/gray] [blue]WHERE[/blue] CustomerID [blue]IN[/blue] [gray]([/gray][red]'2,5,7,42'[/red][gray])[/gray]

[color #008080]--which is not the same thing as[/color]

[blue]SELECT[/blue] [gray]...[/gray] [blue]WHERE[/blue] CustomerID [blue]IN[/blue] [gray]([/gray]2[gray],[/gray]5[gray],[/gray]7[gray],[/gray]42[gray])[/gray]

[red]'42'[/red] = [red]'2,5,7,42'[/red] ? -- No

CREATE Function Split(
   @InputText Varchar(4000), [color #008080]-- The text to be split into rows[/color]
   @Delimiter Varchar(10)) [color #008080]-- The delimiter that separates tokens.
                           -- Can be multiple characters, or empty[/color]

RETURNS @Array TABLE (
   TokenID Int PRIMARY KEY IDENTITY(1,1), [color #008080]--Comment out this line if
                                          -- you don't want the
                                          -- identity column[/color]
   Value Varchar(4000))

AS

[b][color #008080]-----------------------------------------------------------
-- Function Split                                        --
--    ò Returns a Varchar rowset from a delimited string --
-----------------------------------------------------------[/color][/b]

BEGIN

   DECLARE
      @Pos Int,        [color #008080]-- Start of token or character[/color]
      @End Int,        [color #008080]-- End of token[/color]
      @TextLength Int, [color #008080]-- Length of input text[/color]
      @DelimLength Int [color #008080]-- Length of delimiter[/color]

[color #008080]-- Len ignores trailing spaces, thus the use of DataLength.
-- Note: if you switch to NVarchar input and output, you'll need to divide by 2.[/color]
   SET @TextLength = DataLength(@InputText)

[color #008080]-- Exit function if no text is passed in[/color]
   IF @TextLength = 0 RETURN

   SET @Pos = 1
   SET @DelimLength = DataLength(@Delimiter)

   IF @DelimLength = 0 BEGIN [color #008080]-- Each character in its own row[/color]
      WHILE @Pos <= @TextLength BEGIN
         INSERT @Array (Value) VALUES (SubString(@InputText,@Pos,1))
         SET @Pos = @Pos + 1
      END
   END
   ELSE BEGIN
      [color #008080]-- Tack on delimiter to 'see' the last token[/color]
      SET @InputText = @InputText + @Delimiter
      [color #008080]-- Find the end character of the first token[/color]
      SET @End = CharIndex(@Delimiter, @InputText)
      WHILE @End > 0 BEGIN
         [color #008080]-- End > 0, a delimiter was found: there is a(nother) token[/color]
         INSERT @Array (Value) VALUES (SubString(@InputText, @Pos, @End - @Pos))
         [color #008080]-- Set next search to start after the previous token[/color]
         SET @Pos = @End + @DelimLength
         [color #008080]-- Find the end character of the next token[/color]
         SET @End = CharIndex(@Delimiter, @InputText, @Pos)
      END
   END
   
   RETURN

END

GO

[b][color #008080]-----------------------------------------------------------
-- Usage Example 1: Simple SELECTs                       --
-----------------------------------------------------------[/color][/b]

   SELECT TokenID, Value
      FROM dbo.Split('This function brought to you by Erik E',' ')


   SELECT TokenID, Value
      FROM dbo.Split('abcdefghijklmnopqrstuvwxyz','')


[b][color #008080]-----------------------------------------------------------
-- For the next two examples, assume existence of        --
--    the table 'Customers'                              --
--    ò with primary key field CustomerID and name field --
--      FullName                                         --
--    ò and the variable @CustomerList contains a comma- --
--      separated list of desired Customer IDs.          --
--                                                       --
-- These examples will almost assuredly not run as is!   --
--                                                       --
-----------------------------------------------------------
-- Usage Example 2: JOIN                                 --
-----------------------------------------------------------[/color][/b]

   SELECT CustomerID, FullName
      FROM Customers C
         INNER JOIN dbo.Split(@CustomerList,',') S
         ON C.CustomerID = S.Value  [color #008080]-- Implicit conversion to int[/color]
      ORDER BY S.TokenID


[b][color #008080]-----------------------------------------------------------
-- Usage Example 3: WHERE IN                             --
--    ò The above join syntax *may* offer superior       --
--      performance on very large tables.                --
--    ò But I've also included this WHERE IN syntax so   --
--      you can see how it is done.                      --
--    ò This method does not allow you to order by       --
--      TokenID.                                         --
-----------------------------------------------------------[/color][/b]

   SELECT CustomerID, FullName
      FROM Customers
      WHERE CustomerID IN (
         SELECT Value FROM dbo.Split(@CustomerList,',')
      )
   

[b][color #008080]-----------------------------------------------------------
-- Usage Example 4: JOIN (With Setup)                    --
--    ò If you would like a more detailed example of how --
--      to use this function, here is code that does it  --
--      including setting up a temporary Customers table --
--      full of fake data.                               --
--    ò It uses the same JOIN as example 2 above.        --
-----------------------------------------------------------[/color][/b]
   
   CREATE TABLE #Customers (
      CustomerID Int PRIMARY KEY,
      FullName Varchar(60))
   
   INSERT INTO #Customers
      SELECT 1, 'Joe's Landscaping And Goat Leasing' UNION
      SELECT 2, 'Hereford And Calves' UNION
      SELECT 3, 'Multiversal Pictures' UNION
      SELECT 4, 'Remote Control Peanut Butter Spreaders ''R'' Us' UNION
      SELECT 5, 'Rent-A-Spy' UNION
      SELECT 6, 'Whale Dairy Products, a Limited Liability Corporation'
   
   DECLARE @CustomerList Varchar(20)
   SET @CustomerList = '5,2,4'
   
   SELECT CustomerID, FullName
      FROM #Customers C
         INNER JOIN dbo.Split(@CustomerList,',') S
         ON C.CustomerID = S.Value
      ORDER BY S.TokenID
   
   DROP TABLE #Customers


[b][color #008080]-----------------------------------------------------------
-- Function Modification Ideas                           --
--    ò I'll leave it to you to figure out how to        --
--      incorporate the suggestions into the function.   --
-----------------------------------------------------------[/color][/b]

[color #008080]-- Ignore extra blanks[/color]
[blue]SET @InputText = LTrim(RTrim(@InputText))[/blue]
INSERT @Array (Value) VALUES ([blue]LTrim(RTrim([/blue]SubString(@InputText, @Pos, @End - @Pos)[blue]))[/blue])

[color #008080]-- Ignore a trailing delimiter[/color]
[blue]IF Right(@InputText, @DelimLength) <> @Delimiter[/blue] SET @InputText = @InputText + @Delimiter

[color #008080]-- Do not return empty rows[/color]
[blue]IF @End - @Pos > 0[/blue] INSERT @Array (Value) VALUES (SubString(@InputText, @Pos, @End - @Pos))

[color #008080]-- Return a different data type
-- (See example 2, as conversion can also be implicit)[/color]
CREATE Function Split[blue]Int[/blue](
   Value [blue]int[/blue]
INSERT @Array (Value) VALUES ([blue]Convert(int,[/blue] SubString(@InputText, @Pos, @End - @Pos)[blue])[/blue])