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

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

Answer 1

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 ([e-mail])
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 2

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