http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
How to pass a list of values or array to SQLServer stored procedure?
| Note: Information & code samples from this article are tested on SQL Server 2005 RTM (Yukon) and found to be working. Will update the article in case of any compatibility issues. |
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, 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. In this article, I'll present some of these techniques. At the end of
the article I will also provide you with links to articles and books, that
discussed the implementation of arrays in T-SQL.
The following examples
are simplified, just to give you an idea of how things work. You may have to
adapt them to suit your needs. Also, the following stored procedures query the
Orders table from the Northwind sample database, that ships with SQL Server 7.0
and 2000. You should be able to create and execute these procedures in
Northwind.
| Method 1: Dynamic SQL (Works in both SQL Server 7.0 and 2000) |
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
|
The above stored procedure receives a list of OrderIDs |
| Method 2: Parsing the comma separated values into a temporary table and joining the temp table to main table (Works in both SQL Server 7.0 and 2000) |
CREATE PROC dbo.GetOrderList2
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #TempList
(
OrderID int
)
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO #TempList (OrderID) VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
#TempList t
ON o.OrderID = t.OrderID
END
GO
GRANT EXEC ON dbo.GetOrderList2 TO WebUser
GO
|
The above stored procedure receives a list of OrderIDs |
| Method 3: Parsing the comma separated values into a table variable and joining the table variable to main table (Works in SQL Server 2000 only) |
CREATE PROC dbo.GetOrderList3
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TempList table
(
OrderID int
)
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @TempList (OrderID) VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
@TempList t
ON o.OrderID = t.OrderID
END
GO
GRANT EXEC ON dbo.GetOrderList3 TO WebUser
GO
|
The above stored procedure receives a list of OrderIDs |
| Method 4: Using XML (Works in SQL Server 2000 only) |
CREATE PROC dbo.GetOrderList4 ( @OrderList varchar(1000) ) AS BEGIN SET NOCOUNT ON DECLARE @DocHandle int EXEC sp_xml_preparedocument @DocHandle OUTPUT, @OrderList SELECT o.OrderID, CustomerID, EmployeeID, OrderDate FROM dbo.Orders AS o JOIN OPENXML (@DocHandle, '/ROOT/Ord',1) WITH (OrderID int) AS x ON o.OrderID = x.OrderID EXEC sp_xml_removedocument @DocHandle END GO GRANT EXEC ON dbo.GetOrderList4 TO WebUser GO |
The above stored procedure receives a list of OrderIDs, in the EXEC dbo.GetOrderList4 ' <ROOT> <Ord OrderID = "10248"/> <Ord OrderID = "10252"/> <Ord OrderID = "10256"/> <Ord OrderID = "10261"/> <Ord OrderID = "10262"/> <Ord OrderID = "10263"/> <Ord OrderID = "10264"/> <Ord OrderID = "10265"/> <Ord OrderID = "10300"/> <Ord OrderID = "10311"/> <Ord OrderID = "11068"/> <Ord OrderID = "11069"/> <Ord OrderID = "11070"/> <Ord OrderID = "11071"/> <Ord OrderID = "11072"/> <Ord OrderID = "11073"/> <Ord OrderID = "11074"/> <Ord OrderID = "11075"/> <Ord OrderID = "11076"/> <Ord OrderID = "11077"/> </ROOT>'Try to keep the element/attribute names in your XML tags as short as possible. This will help keep the document size small, and could improve parsing time. Also, the smaller the XML document, the lesser time it takes to travel over the network (from your application to your database server). Bear in mind that XML is case sensitive. This SQLXML functionality available in SQL Server 2000 is very powerful and the above stored procedure is just a simple example. Be sure to check your query execution plan when joining tables with OPENXML output. If you see index scans, and the table is large, then you might want to dump the OPENXML output into a temporary table, and join that to your main table. This would more likely result in an index seek, provided you have the right index. You'll find a link to an SQL XML book, at the end of this article. |
| Method 5: Using a table of numbers or pivot table, to parse the comma separated list (Works in SQL Server 7.0 and 2000) |
--Create a table called Numbers
CREATE TABLE dbo.Numbers
(
Number int PRIMARY KEY CLUSTERED
)
GO
--Insert 8000 numbers into this table (from 1 to 8000)
SET NOCOUNT ON
GO
DECLARE @CTR int
SET @CTR = 1
WHILE @CTR < 8001
BEGIN
INSERT INTO dbo.Numbers (Number) VALUES (@CTR)
SET @CTR = @CTR + 1
END
GO
--The above two steps are to be run only once. The following stored procedure uses the number table.
CREATE PROC dbo.GetOrderList5
(
@OrderList varchar(1000)
)
AS
BEGIN
SET NOCOUNT ON
SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
(
SELECT LTRIM(RTRIM(SUBSTRING(OrderID, number+1, CHARINDEX(',', OrderID, number+1)-number - 1))) AS OrderID
FROM
(
SELECT ',' + @OrderList + ',' AS OrderID
) AS InnerQuery
JOIN
Numbers n
ON
n.Number < LEN(InnerQuery.OrderID)
WHERE SUBSTRING(OrderID, number, 1) = ','
) as Derived
ON o.OrderID = Derived.OrderID
END
GO
GRANT EXEC ON dbo.GetOrderList5 TO WebUser
GO
|
The above stored procedure receives a list of OrderIDs |
| Method 6: Using a general purpose User Defined Function (UDF) to parse the comma separated OrderIDs (Works in SQL Server 2000 only) |
--The following is a general purpose UDF to split comma separated lists into individual items.
--Consider an additional input parameter for the delimiter, so that you can use any delimiter you like.
CREATE FUNCTION dbo.SplitOrderIDs
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
RETURN
END
GO
CREATE PROC dbo.GetOrderList6
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
dbo.SplitOrderIDs(@OrderList) AS s
ON
o.OrderID = s.OrderID
END
GO
GRANT EXEC ON dbo.GetOrderList6 TO WebUser
GO
|
The above script creates a Multi-statement table-valued user |
Top
Here are the links to additional information,
followed by links to related books: