sql - Sane/fast method to pass variable parameter lists to SqlServer2008 stored procedure

Question

A fairly comprehensive query of the brain has turned up a thousand and one ways to pass variable length parameter lists that involve such methods as:

• CLR based methods for parsing strings to lists of integers
• Table valued functions that require the presence of a 'Numbers' table (wtf?)
• Passing the data as XML

Our requirements are to pass two variable length lists of integers (~max 20 ints) to a stored procedure. All methods outlined above seem to smell funny.

Is this just the way it has to be done, or is there a better way?

Edit: I've just found this, which may qualify this question as a dupe

Answer 1

Yes, I'd definitely look at Table Valued Parameters for this. As a side benefit, it may allow you to use a nice, clean set-based implementation for the innards of your procedure directly, without any data massaging required.

Here's another reference as well...