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)
)
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
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
BEGINSET 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