I think this use case is better managed using client side dynamic sql generation. I'm not talking about concatenating strings to build the sql statement. I'm refering to using ORM's such as EntityLite https://github.com/jesuslpm/EntityLite:

for example:

var orderIds = new List<int> { 10248, 10248, 10250 };

var orders = dataService.OrderRepository.CreateQuery(Projection.BaseTable)

.Where(OrderFields.OrderId, OperatorLite.In, orderIds)

.ToList();

Advantages:

* No XML parsing

* No table variables

* Efficiency

* Easier

Limitations:

* Max number of id = max number of allowed parameters.

I think stored procedures are better suited for set based modifications, but not for perform dynamic queries.