none
Is it possible Select any number of OrderIDs and send the criteria to a stored procedure? RRS feed

  • Question

  • I'm trying to select any number of orders and call a stored procedure only one time.  So my question is in order to peform a select statement in  a stored procedure, is it possible to have n number of parameters, and if it is possible what is the best way to call a stored procedure with any number of orderIds?  Or is it better to send in a nvarchar 30000 with xml and parse the xml for each order id and stick it into my stored procedure that way?  I'm looking for a better way to perform this operation that is reliable.

    example:

    select * from tblOrders where OrderId = 1 or OrderId = 2 or OrderId = 1000 etc...

    Marc Noon

    Marc Noon
    Thursday, April 21, 2011 1:17 AM

Answers

  • You can create a temp table:

    CREATE TABLE #temptblOrders(OrderID int)
    

    ... store all OrderIDs into this.

     

    Use this temp table inside your proc as:

    select * from tblOrders where OrderId in (select OrderID from #temptblOrders)
    


    ~Manoj (manub22@gmail.com)
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    • Marked as answer by mnoon Thursday, April 21, 2011 2:07 AM
    Thursday, April 21, 2011 1:53 AM

All replies

  • You can create a temp table:

    CREATE TABLE #temptblOrders(OrderID int)
    

    ... store all OrderIDs into this.

     

    Use this temp table inside your proc as:

    select * from tblOrders where OrderId in (select OrderID from #temptblOrders)
    


    ~Manoj (manub22@gmail.com)
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    • Marked as answer by mnoon Thursday, April 21, 2011 2:07 AM
    Thursday, April 21, 2011 1:53 AM
  • If you're using SQL Server 2008, then look at using table valued parameters. In other words, you'll pass table with the orderIDs as a parameter to your SP.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, April 21, 2011 2:51 AM
    Moderator