How to Add an array of ints to stored procedure in SQL Server 2000

Is it possible to send n ints to a stored procedure as parameters? The reason is because I need to select n orders and call a sproc to then send back only those order ids I selected.

I know how to call one, but I would prefer to call a set of orders w开发者_如何学Cith row size n if possible.

select * 
from order 
where orderid =1 or orderid = 2 or orderid = 100 ...

---

It's possible to send n ints but not as parameters unfortunately.

SELECT * FROM Order WHERE orderid in (1, 2, 100)

---

You can convert your comma separated parameter to XML, use that to fill a table variable of ID's that you then can use in your queries.

create procedure GetOrderList @OrderIDs varchar(8000)
as
-- Convert paramter to xml
declare @XMLStr varchar(8000)
set @XMLStr = '<R><I ID="'+replace(@OrderIDs, ',', '"></I><I ID="')+'"></I></R>'

-- Table variable that holds the ID's
declare @IDs table (ID int)

-- Load the XML document and insert id's to @IDs
declare @idoc int
exec sp_xml_preparedocument @idoc out, @XMLStr
insert into @IDs
    select ID from openxml(@idoc, '/R/I',1) with (ID  int)
exec sp_xml_removedocument @idoc

-- Use @IDs in your query
select O.OrderID
from [order] as O
  inner join @IDs as I
    on O.OrderID = I.ID 

Test

exec GetOrderList '1,2,3,4'

Result

OrderID
-------
1
2
3
4