Post reply

Best practice for passing multiple values into stored procedure

  • September 1, 2009 at 9:23 am

    #135294

    Hello everyone,

    I was just curious, what is the best practice for passing multiple values into a stored procedure? For example, say the WHERE clause is an IN statement to get either a single product or multiple products (ie WHERE products IN (@productID) @productID is the input parameter. Passing 1 product ID works fine, but what practices should I implement so that multiple values can be passed?

    I did some Googling and found a few samples where people pass a comma delimited string (ie '12, 54, 87'), but it seems like there should be a better more efficient way to do it.

    Thanks,

    Strick

  • September 1, 2009 at 9:34 am

    #1047616

    As well as comma delimited strings, you can use XML

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • September 1, 2009 at 9:51 am

    #1047629

    When you get to 2008, you will also be able to pass table variables as input parameters. However, we have a lot of code that parses strings to get sets of variables. For example, 1-N zip codes could be passed in a single variable. The receiving procedure typically parses the values out of the string into a temporary table or table variable, which is then used in a JOIN instead of using a WHERE clause.

    Using a tally table to parse the string into a table runs efficiently, and our UI developers are quite happy to just build a single string such as '|90210|90211|90255|'.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • September 3, 2009 at 8:41 am

    #1048791

    I've heard that Adam Machanic may have a few CLR procedures that speed up the process of submitting multiple values. He mentioned it yesterday during his 24 Hours of PASS presentation. You might check around on his blog.

    Otherwise, I'd suggest the tally table approach. Jeff Moden has written about it quite a bit. You can look up his name here on SSC.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply