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

http://vyaskn.tripod.com/passing_アロイスto_stored_procedures.httm
 
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 and found to be working.Will udate the article of any comptibility ises.
unfortunary，there isのbuilt-in support for arrays in SQL Server's T-SQL.SQL Server 2000 did some new datatypes like sql_variant，bigint etc，but no support for the mucneedarrays. The e arararararararararararararararararararararararararararararararararararararararararararararararararararararararararararare some situatis，that rererequrequire the the the stststrererererererererererererererererererererererererererererererererererereeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeethis case，passing a list of selected order numbers to the stored procedure、in one go、and getting the relt back is more efficient、copared to cared stored procedue for each selected order number.Since、we campration ofwe need to look for work workounds and alternative.Over the years，programmers developed different techniques，some of which are not so efficient，some efficient，but compplex.The most popurteniques of partis。the normal input parameter of the stored procedure receives a list of say、OrderIDs、separated bycomas.In thisaricle、I'll present some of these techniques.At the end of the arararartititititimile I will alalalalproproproprodededesasasasasasasasasasasasasasasasas.InInInInInInInInInInInInInInInInInInInInInInInInInInInInInInInstststststststststrererererererererererererererererererererererererererererererererejust to give you an idea of how things work.You may have to adappt them to suit your needs.Also，the follwing stored procedures query the Orders the Northwind sample database，that the ships Sable 2000。
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 bycommmas，as an input parameter.It then dynamically construct an SQL statement and executes it using EXEC.Dynamital SQL haits themitations，Fortiscommmmind in the abobobobobobove script.That statement grants EXECUTE permission to the user WebUser.Butthththththat is is not enough forWebUser to eeeeeeexstored procedure.The euser eeeeeeeeeeeeemimimimimimimimimimimimimimimic SQmmmmmmmttttttddddmimimimimimimimimimimimimimimimimimimimimimimimimimimimimimimimimimimimimimimimic Sssssseeeedededededelimitation、I added a「GRANT SELECT」commmand in the aboboboboboboboboboboboboboboboboboboboboboboboboboboboboboboboott、to enablle WebUser to run the storord the storored procedurerererererererere. OrderID、CstststomerID、EmplodedededeID、EmplodededededeID、EmploedededededededeID、Ededededededededededededed dededededededededededededededededededededededededededededededededededededededededededededededede10263,10264,10265,1030,10331'GONote that，dynamic SQL is vulnerabale to SQL Injection、a technique using which a malicious user could inject his own code into your dynamic SQL sting and get it executed.Try this example、and see what happens EXrectSELECT*FROM syssobjecs-'GOThe re are other limitations that apply to dynamic SQL.Check out the link at the end of this artic，for additional information on using dynamic SQL.
Method 2：Parssing the comma separated values in to a temporary table and joining the temp table to man 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 aboff stored procedure receives a list of OrderIDs separated bycommmas、as an input parameter.It then parses the parameter、extracs individual Order Order IDs from the commsema parated list、ints the the therdaremparemas theto get the requested rerererererererererererererererererererererereltts...cstomerID、EmployeID and columns for the giventordenumbers65：EXEC dbod.GeorororordededededededededededededededededededededededededededededededededededededededededededededededededededededededededededededeID：EXEC：EXEC ddbobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobobostored procedure，sometimes restored procedure recomplations.You can find a link at the end of this artic le，that has more information on this topic.You could verify is using Profiler.Also，T-SQL string functions。
Method 3：Parssing the commaparated values in to 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 aboff stored procedure receives a list of Orderids separated bycommmas、as an input parameter.It then parses the parameter、extracs individual Orderids from the commsema parated list、ints theabthe ablesto get the requested requested results.cal this stored procedure as shown below，and it will retrieve OrderID，CustoomerID，EmployeeID and OrderDate columns for the given ordenumbers: EXEC dbo.GetOrderList 3'10248,10252,10256,10261,10262,10263,10264,10265,1030100,103311'goTablblblables could could commbobobobobod could cocococommmmmmmbobobobobobobobobobos cococommmmmmmmmmmblblblblblblemimimimimimiclclclclclclclclickekekekekekekes cocococococococococococococococococococococococococococoing could consume more CPU cycles、if the list is huge.At the end of this artic 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 abook 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 uxml_preparedocument and OPRENXML rowset provider，joins the output to the main Orders table to retrieve the order information n.CutomerID，and it will retrieve Order IDEmployeeID and OrderDate columns for the given ordenumbers:

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/atribute names in your XML stas s s s s shotasas possiblble.This will help help keep the docusiment the the the attttttcusisisizzzment the proproproprotitititititisssssssssssssssle le le le le le le thethethethethethethethethethethethethethethethethethethethethemamamamatitititititttttttmmmmmnetwork（from your appication to your database server）Be a Bear in mind that XML is is case sensitive.ThisSQLXML functititinalityavailable in SQL Server 2000 is powerful and the above stororeeemple.Be sure to chchchchcheekukukukukukukuryeeeeeeeexxxxeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeexxxxxxxxxxeeeeeeeeeeeeeeexxxxxxxxxxxxxeeeeeeeeeeeeeeeeeeeeeedump the OPEXML outputin to a temporary table，and join that to your man table.This would more likely recut in in index seek，provided you have the right index.You'll find a link to an SQL XML book，at the end of this artic. 
Method 5：Using a table of numbers or pivot table、to parse the comme 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 bycommmas、as an input parameter.It then parses the parameter、in just one query、using the number tablejoins the indidividual OrderIDs to the OrderIDs fffrom Orders table to retrieve the rerererererereretreeve the rerererererererereretrieve the rerererererererererererererererererererererererererererererererererererererererererererererererererererererererererererererererererererererererererererererererereretreeeeeeeeeeeeeve thererererererererererererererererererererererererererererere10264,10265,1030,103311'GO
Method 6：Using a general purpose User Defined Function（UDF）to parse the commer 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 usdefined function，that accepts comaseparated lists，and split the list inindividudual its and returns them them mastlar format.The storororored prorererererererererereststststststststststrerererererererererererererererererereaaaatotototoststststststststststststrerererererererererererererererererererererererererererererererererererererereshown below、and it willretrieve OrderID、CustoomerID、EmployeeID and OrderDate columns for the given order numbers：EXEC dbo.GetOrder List 6'10248,10252,10261,10262,10264,10265,1030000,103311'GO
links to articales and book s Here are the links to additional information、フォローアップby links to related book s:
トップ
The curse and bleassings of dynamic SQL，by Errand Sommarskyog（SQL Server MVP）
A more complex example of OPEXML usage，by Linda（SQL Server MVP）
INF：Trouble shoting Stred Procesdure Recomppilation（Q 243586）