Introduction
This code will show how to pass an array of values to SQL server procedure.
Background
I have seen many samples on the web for passing an array to a stored procedure, then I used these two methods that can be used in both SQL 2005 and 2008.
Using the Code
Method 1 Can be Used in Both 2005 and 2008
Create a temp table using string concatenation, then use it in the procedure:
USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetOrderDetailsUsingTempTable]
@Products nvarchar(max)
as
create table #ProductIDs
(ProductID bigint)
Declare @ProductsSQL nvarchar(max);
Select @ProductsSQL = 'Insert into #ProductIDs (ProductID) _
SELECT [ProductID] FROM [Products] WHERE (ProductID in (' + @Products + '))'
exec sp_executesql @ProductsSQL
SELECT [OrderID]
,[ProductID]
,[UnitPrice]
,[Quantity]
,[Discount]
FROM [Northwind].[dbo].[Order Details]
where ProductID in (select ProductID from #ProductIDs)
Method 2 Can be Used in 2008
Create a function that returns a table and takes an XML parameter:
USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetDT] ( @Xml xml )
RETURNS @DT TABLE
(
ID nvarchar(max)
)
AS
BEGIN
INSERT INTO @DT (ID)
SELECT ParamValues.ID.value('.','nvarchar(max)')
FROM @xml.nodes('/table/id') as ParamValues(ID)
RETURN
END
Make sure that the XML parameter has the same root name and node name and in the same case:
<table><id>1</id><id>2</id><id>3</id><id>4</id></table>
Then do this:
USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetOrderDetailsUsingXML]
@XML nvarchar(max)
as
SELECT [OrderID]
,[ProductID]
,[UnitPrice]
,[Quantity]
,[Discount]
FROM [Northwind].[dbo].[Order Details]
where ProductID in (select id from dbo.GetDT(@XML))
Form Section
private void button1_Click(object sender, EventArgs e)
{
try
{
DataTable DT = new DataTable();
String StrCon = System.Configuration.ConfigurationManager.ConnectionStrings
["PassingAnArrayToStoredProcedure.Properties.Settings.
NorthwindConnectionString"].ConnectionString;
using (SqlConnection Con = new SqlConnection(StrCon))
{
using (SqlCommand Cmd = new SqlCommand("GetOrderDetailsUsingTempTable", Con))
{
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@Products", ProductsIDs());
using (SqlDataAdapter DA = new SqlDataAdapter(Cmd))
{
DA.Fill(DT);
}
}
}
dataGridView2.DataSource = null;
dataGridView2.DataSource = DT;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private String ProductsIDs()
{
StringBuilder SB = new StringBuilder();
foreach (DataGridViewRow DGV in dataGridView1.Rows)
{
DataGridViewCheckBoxCell Chk = (DataGridViewCheckBoxCell)DGV.Cells[0];
{
if (Chk != null)
{
if ((Boolean)Chk.FormattedValue == true)
{
SB.Append(DGV.Cells[1].Value.ToString() + ",");
}
}
}
}
String Result = SB.ToString();
SB.Remove(0, SB.Length);
char x = ',';
return Result.TrimEnd(x);
}
private string ParseStringToXml(string Str, char Separator,
string root, string ElementName)
{
string Xml = string.Empty;
if (!(Str.Trim() == string.Empty))
{
char[] ArrSeparator = {Separator};
string[] Arr = Str.Split(ArrSeparator);
System.IO.StringWriter TxtWriter = new System.IO.StringWriter();
XmlTextWriter XmlWriter = new XmlTextWriter(TxtWriter);
XmlWriter.WriteStartElement(root);
for (int Index = 0; Index <= Arr.Length - 2; Index++)
{
XmlWriter.WriteStartElement(ElementName);
XmlWriter.WriteString(Arr[Index].Trim());
XmlWriter.WriteEndElement();
}
XmlWriter.WriteEndElement();
Xml = TxtWriter.ToString();
}
return Xml;
}
private void button2_Click(object sender, EventArgs e)
{
try
{
DataTable DT = new DataTable();
String StrCon = System.Configuration.ConfigurationManager.ConnectionStrings
["PassingAnArrayToStoredProcedure.Properties.Settings.
NorthwindConnectionString"].ConnectionString;
using (SqlConnection Con = new SqlConnection(StrCon))
{
using (SqlCommand Cmd = new SqlCommand("GetOrderDetailsUsingXML", Con))
{
Cmd.CommandType = CommandType.StoredProcedure;
string XMLParam = ParseStringToXml( ProductsIDs() , ',' , "table" , "id");
Cmd.Parameters.AddWithValue("@XML", XMLParam);
using (SqlDataAdapter DA = new SqlDataAdapter(Cmd))
{
DA.Fill(DT);
}
}
}
dataGridView2.DataSource = null;
dataGridView2.DataSource = DT ;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
The attached zip file holds all the files for this process. Hope it helps.
Points of Interest
Using the Table
function method is very helpful and much easier.
History
- 13th May, 2009: Initial post