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.
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.
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.
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 separated by commas, as an input parameter. It then dynamically constructs an SQL statement and executes it using EXEC.
Dynamic SQL has its limitations, and is not something I would recommend. For starters, notice the "GRANT EXEC" command in the above script. That statement grants EXECUTE permission to the user WebUser. But that is not enough for WebUser to execute this stored procedure. The user executing dynamic SQL commands needs explicit permissions on the underlying tables, which is not something I would do on a production system. Because of this limitation, I added a "GRANT SELECT" command in the above script, to enable WebUser to run the stored procedure. Call this stored procedure as shown below, and it will retrieve OrderID, CustomerID, EmployeeID and OrderDate columns for the given order numbers: EXEC dbo.GetOrderList1 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311' GONote that, dynamic SQL is vulnerable to SQL Injection, a technique using which a malicious user could inject his own code into your dynamic SQL string and get it executed. Try this example, and see what happens: EXEC dbo.GetOrderList1 '0); SELECT * FROM sysobjects --' GOThere are other limitations that apply to dynamic SQL. Check out the link at the end of this article, for additional information on using dynamic SQL. |
| 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 separated by commas, as an input parameter. It then parses the parameter, extracts individual OrderIDs from the comma separated list, inserts the OrderIDs into a temporary table, and then joins the temporary table with the main Orders table, to get the requested results.
Call this stored procedure as shown below, and it will retrieve OrderID, CustomerID, EmployeeID and OrderDate columns for the given order numbers: EXEC dbo.GetOrderList2 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311' GOThe creation of temporary tables inside a stored procedure, sometimes results in stored procedure recompilations. You can find a link at the end of this article, that has more information on this topic. You could verify this using Profiler. Also, T-SQL string functions are not very efficient, so the parsing could take more CPU cycles with large lists. |
| 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 separated by commas, as an input parameter. It then parses the parameter, extracts individual OrderIDs from the comma separated list, inserts the OrderIDs into a table variable, and then joins the table variable with the main Orders table, to get the requested results.
Call this stored procedure as shown below, and it will retrieve OrderID, CustomerID, EmployeeID and OrderDate columns for the given order numbers: EXEC dbo.GetOrderList3 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311' GOTable variables could be quicker compared to temporary tables. Table variables don't have the limitation of recompilations, unlike method 2. But the parsing could consume more CPU cycles, if the list is huge. At the end of this article you'll find a link to an FAQ on table variables. |
| 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 form of an XML document, as an input parameter. It then parses the XML document using sp_xml_preparedocument and OPENXML rowset provider, joins the output to the main Orders table to retrieve the order information.
Call this stored procedure as shown below, and it will retrieve OrderID, CustomerID, EmployeeID and OrderDate columns for the given order numbers: 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>'
GO
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 separated by commas, as an input parameter. It then parses the parameter, in just one query, using the number table (unlike WHILE loop in previous examples) and joins the individual OrderIDs to the OrderIDs from Orders table to retrieve the results.
Call this stored procedure as shown below, and it will retrieve OrderID, CustomerID, EmployeeID and OrderDate columns for the given order numbers: EXEC dbo.GetOrderList5 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311' GO |
| 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 defined function, that accepts comma separated lists, and splits the list into individual items and returns them in tabular format.
The stored procedure is almost similar to the one in method 2, except that it uses the UDF in the JOIN. Call this stored procedure as shown below, and it will retrieve OrderID, CustomerID, EmployeeID and OrderDate columns for the given order numbers: EXEC dbo.GetOrderList6 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311' GO |
No comments:
Post a Comment