How to pass a list of values or array to SQL Server stored procedure?

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, on submit, retrieves complete information about the selected orders. In this case, passing a list of 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 so efficient, some 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.

Dynamic SQL :

CREATE PROC dbo.GetOrderList1
(
                    @OrderList varchar(500)
)

AS

BEGIN
                    SET NOCOUNT ON
                    DECLARE @SQL varchar(600)
                    SET @SQL = 'SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM dbo.Orders                   WHERE OrderID IN (' + @OrderList + ')'
                    EXEC(@SQL)              
END
GO

GRANT EXEC ON dbo.GetOrderList1 TO WebUser

GO

GRANT SELECT ON dbo.Orders TO WebUser

GO