Click here to Skip to main content
Click here to Skip to main content

Passing an Array as Parameter to SQL Server Procedure

, 13 May 2009
Rate this:
Please Sign up or sign in to vote.
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Mohammad Al Hoss
Software Developer CME Offshore
Lebanon Lebanon
No Biography provided

Comments and Discussions

 
GeneralFound NAswer to my question PinmemberVijayaNeeraj4-Nov-13 13:30 
GeneralMy vote of 5 Pinmemberdb_developer23-Sep-12 1:53 
GeneralMy vote of 1 Pinmembereghetto16-Jul-12 22:56 
GeneralMy vote of 1 PinmemberManuIS25-Mar-10 1:43 
General[My vote of 2] why not u use OpenXML PinmemberAbhishek Sur25-May-09 0:13 
GeneralPerformance PinmemberJcmorin19-May-09 9:42 
GeneralRe: Performance PinmemberMohammad Al Hoss30-May-09 1:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 13 May 2009
Article Copyright 2009 by Mohammad Al Hoss
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid