Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

Database News

Posted Dec 5, 2002

How to Pass a List/Array to a Stored Procedure in SQL Server

By DatabaseJournal.com Staff

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.

Back to Database Journal Home

Database News Archives

Latest Forum Threads
Database News Forum
Topic By Replies Updated
Efficient SQL Server Indexing by Design lcole 0 April 30th, 12:38 PM
Mine Oracle Database, SQL Server and Other Databases with Monarch Data Pump Pro V10.5 lcole 0 April 30th, 12:37 PM
Oracle Database and Oracle Fusion Middleware for Private Social Network Application lcole 0 April 30th, 12:31 PM
Oracle Database Maintains a Stronghold in the DBMS Market lcole 0 April 30th, 12:30 PM