How to Pass a List/Array to a Stored Procedure in SQL Server
December 5, 2002
Unfortunately, there is no built-in support for arrays in SQL Server's T-SQL. SQL Server 2000 did add some new datatypes like sql_variant, bigint, etc., but no support for the much needed arrays. There are some situations that require the ability to pass a list of values to a stored procedure. Think about a web page that lets the user select one or more of his/her previous orders and, upon a submit by the user, retrieves complete information about the selected orders. In this case, passing a list of the selected order numbers to the stored procedure in one go and getting the results back is more efficient compared to calling the same stored procedure for each selected order number.
Since we cannot create arrays of variables or input parameters or columns in T-SQL, we need to look for workarounds and alternatives. Over the years, programmers developed different techniques, some of which are not very efficient, while others are efficient but complex. The most popular technique is to pass in a list of values, separated by commas (CSV). With this method, the normal input parameter of the stored procedure receives a list of say, OrderIDs, separated by commas. In this article, Narayana Vyas Kondreddi presents some of these techniques. At the end of the article Kondreddi also provides the reader with links to articles and books that discuss the implementation of arrays in T-SQL.
The complete article is available at http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm.