INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

Passing a list of values to a Stored Procedure (Part II) by ESquared
Posted: 14 Jun 04 (Edited 30 Apr 09)

The SQL FAQ, Passing a list of values to a Stored Procedure (Part I), has a good discussion of various ways to get the job done.

Update July 2007 - see Passing a list of values to a Stored Procedure (Part III) for two more options. It discusses handling extremely long lists and also offers a faster function than the one given below.

This Part II exists because I wanted to offer an additional method that I believe is more efficient and easier to use, with clearer examples.

Here is a quick rundown of Part I:

  • Option 1 - Dynamic SQL

  • Not so great, as it says. Try to avoid dynamic SQL whenever possible!

  • Option 2 - Table-valued Function

  • A good idea, but in my opinion, the referenced function on MSDN was poorly implemented. It's more complicated than it needs to be in format and coding, the attached article is very long, the instructions for using it are very short, and the comments, well, I'll shut up now. [Update: the article is long gone from MSDN.]

  • Option 3 - Temp Table
    A temp table disappears (or can't be 'seen' in the scope of the calling context) the moment the creating procedure exits. So to use this option one must:
    1. Create a temp table,
    2. run the stored procedure in the same scope,
    3. use its output,
    4. then finally drop or truncate the temp table, especially if you want to use the sp again.
    And that is a little bit awkward for frequent use.



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

  • Option 4 - A More Efficient Table Function


  • The goal is to be able to do something similar to the following:

    CODE

    SELECT ... WHERE CustomerID IN @List

    If you've tried it, you know by now that it doesn't work. If your list is the text string '2,5,7,42' then the above statement is essentially saying

    CODE

    SELECT ... WHERE CustomerID IN ('2,5,7,42')

    --which is not the same thing as

    SELECT ... WHERE CustomerID IN (2,5,7,42)

    So the query engine gets to customer 42 and tries to match it to your string. First it has to convert the integer column value to a string, so it does this: 42 to '42'. Then it compares this string to the string of four customer numbers:

    CODE

    '42' = '2,5,7,42' ? -- No
    The row is not included.

    I put the code in the colors that Query Analyzer uses so you can quickly see the difference between how it interprets the two statements. The first has the list as a string (red), the second has them as separate number literals (black). The parser would be happy with integer variables separated by commas, but not a string.

    So what do you do? Keep reading: I've got a function for you to easily get the job done.

    Several other similar UDFs I've seen on the web have done special handling for spaces, trimming whitespace from input strings before parsing, and from output strings after parsing. Some have also ignored a lone trailing delimiter. I prefer to let the function user make such decisions. You of course can add your own modifications to make the function suit your needs, and I've included some ideas at the end on how to do this.

    It is named Split in honor of the Visual Basic built-in function which has the same functionality. Usage examples are given after the function.

    CODE

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


    RETURNS @Array TABLE (
       TokenID Int PRIMARY KEY IDENTITY(1,1), --Comment out this line if
                                              -- you don't want the
                                              -- identity column

       Value Varchar(4000))

    AS

    -----------------------------------------------------------
    -- Function Split                                        --
    --    • Returns a Varchar rowset from a delimited string --
    -----------------------------------------------------------


    BEGIN

       DECLARE
          @Pos Int,        -- Start of token or character
          @End Int,        -- End of token
          @TextLength Int, -- Length of input text
          @DelimLength Int -- Length of delimiter

    -- 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.

       SET @TextLength = DataLength(@InputText)

    -- Exit function if no text is passed in
       IF @TextLength = 0 RETURN

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

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

    END

    GO

    -----------------------------------------------------------
    -- Usage Example 1: Simple SELECTs                       --
    -----------------------------------------------------------


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


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


    -----------------------------------------------------------
    -- 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                                 --
    -----------------------------------------------------------


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


    -----------------------------------------------------------
    -- 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.                                         --
    -----------------------------------------------------------


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

    -----------------------------------------------------------
    -- 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.        --
    -----------------------------------------------------------

       
       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


    -----------------------------------------------------------
    -- Function Modification Ideas                           --
    --    • I'll leave it to you to figure out how to        --
    --      incorporate the suggestions into the function.   --
    -----------------------------------------------------------


    -- Ignore extra blanks
    SET @InputText = LTrim(RTrim(@InputText))
    INSERT @Array (Value) VALUES (LTrim(RTrim(SubString(@InputText, @Pos, @End - @Pos))))

    -- Ignore a trailing delimiter
    IF Right(@InputText, @DelimLength) <> @Delimiter SET @InputText = @InputText + @Delimiter

    -- Do not return empty rows
    IF @End - @Pos > 0 INSERT @Array (Value) VALUES (SubString(@InputText, @Pos, @End - @Pos))

    -- Return a different data type
    -- (See example 2, as conversion can also be implicit)

    CREATE Function SplitInt(
       Value int
    INSERT @Array (Value) VALUES (Convert(int, SubString(@InputText, @Pos, @End - @Pos)))

    --- Many thanks to donutman for his ideas and comments on this FAQ ---

    Back to Microsoft SQL Server: Programming FAQ Index
    Back to Microsoft SQL Server: Programming Forum

    My Archive

    Resources

    Close Box

    Join Tek-Tips® Today!

    Join your peers on the Internet's largest technical computer professional community.
    It's easy to join and it's free.

    Here's Why Members Love Tek-Tips Forums:

    Register now while it's still free!

    Already a member? Close this window and log in.

    Join Us             Close