65.9K
CodeProject is changing. Read more.
Home

Passing an Array as Parameter to SQL Server Procedure

starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.39/5 (10 votes)

May 13, 2009

CPOL
viewsIcon

86066

downloadIcon

495

Passing an array as parameter to SQL server Procedure

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

untitled.JPG

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

  • 13th May, 2009: Initial post