Archive for April 21, 2011

Select multiple number of OrderIDs and send the criteria to a Stored Procedure parameter – MSDN TSQL forum

April 21, 2011 Leave a comment

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


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


–> Answer:

Yes, not only XML, but there are various ways to pass multiple values to a Stored Proc via parameters, like:
1. Passing CSV string
2. Passing an XML or JSON string
3. Using temp-table
4. Using TVPs
… check here for all these options, link.

You can go with by creating a temp table:

CREATE TABLE #temptblOrders (OrderID int)

… store all OrderIDs into this.

Use this temp table inside your proc as:

FROM tblOrders 
WHERE OrderId in (
	FROM #temptblOrders


Ref link.