SQL with Manoj

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

example:

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:

SELECT * 
FROM tblOrders 
WHERE OrderId in (
	SELECT OrderID 
	FROM #temptblOrders
)

Ref link.