Passing an Array as Parameter to SQL Server Procedure

• Download source - 56.76 KB

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
/****** Object: StoredProcedure [dbo].[GetOrderDetailsUsingTempTable] 
Script Date: 05/13/2009 10:05:20 ******/
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
/****** Object: UserDefinedFunction [dbo].[GetDT] Script Date: 05/13/2009 10:05:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
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
/****** Object: StoredProcedure [dbo].[GetOrderDetailsUsingXML] 
Script Date: 05/13/2009 09:53:48 ******/
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);
}
/// <summary> 
/// Parse a formatted string to XML format 
/// </summary> 
/// <param name="Str"></param> 
/// <param name="ElementName"></param> 
/// <param name="Separator"></param> 
/// <returns></returns> 
/// <remarks></remarks> 
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

• 13^th May, 2009: Initial post