[转载]：How to pass a list of values or array to SQL Server stored procedure?

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

 

How to pass a list of values or array to SQL
Server 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)

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'
GO

Note
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
--'
GO

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

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'
GO

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

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'
GO

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

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:

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)

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

Top
Here are the links to additional information,
followed by links to related books:





The curse and blessings of dynamic SQL, by Erland
Sommarskog (SQL Server MVP)

A more complex example of
OPENXML usage, by Linda (SQL Server MVP)

INF: Troubleshooting Stored
Procedure Recompilation (Q243586)

INF: Frequently Asked Questions
- SQL Server 2000 - Table Variables (Q305977)

转载于:https://www.cnblogs.com/Jessy/archive/2011/07/28/2119192.html