Pass Array to a Stored Procedure

Simple Method to Pass Array to a Stored Procedure - SQL Server Side

Consider the following simple method for defining the stored procedure using dynamic SQL. The array parameter is defined simply as a string and the input will be expected to be comma-delimited. By forming the sql dyanmically with the input string, we can query against the values in the array by using the IN command.

CREATE PROCEDURE [dbo].[GetData]

@MyCodes as varchar(500) = '', -- comma delimited list of codes, ie: '''ABC'', ''DEF'', ''GHI'''

AS

BEGIN

DECLARE @query as nvarchar(500)

set @query = 'SELECT * FROM DATA WHERE Code IN (@p_MyCodes)'

exec SP_EXECUTESQL @query,

N'@p_MyCodes varchar(500)',

@p_MyCodes = @MyCodes

END

The above stored procedure definition will accept a comma-delimited string, which we process as an array using the SQL IN command. Note, we had to use dyanmic SQL to properly form the query (which involves expanding the comma-delimited string).

Simple Method to Pass Array to a Stored Procedure - C# .NET Side

Next, we need to define the method to pass the data and execute the stored procedure from C# .NET.

The first step is to convert our array of data into a comma-delimited string, which is what the stored procedure expects to receive. Depending on your data type, this code may vary. For this example, we are using a .NET collection.

string myCodes = string.Empty; // Initialize a string to hold the comma-delimited data as empty

foreach (MyItem item in MyCollection)

{

if (myCodes.Length > 0)

{

myCodes += ", "; // Add a comma if data already exists

}

myCodes += "'" + item.Name + "'";

}

The code above will create a string in the following format:

'One','Two','Three'

Now that the collection has been converted to a string, we can pass the value as a parameter to the stored procedure by using the following code:

using System;

using System.Data;

using System.Data.SqlClient;

SqlConnection MyConnection = null;

SqlDataReader MyReader = null;

try

{

// Create the SQL connection.

MyConnection = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI"))

MyConnection.Open();

// Create the stored procedure command.

SqlCommand MyCommand = new SqlCommand("GetData", MyConnection);

// Set the command type property.

MyCommand.CommandType = CommandType.StoredProcedure;

// Pass the string (array) into the stored procedure.

MyCommand.Parameters.Add(new SqlParameter("@MyCodes", myCodes));

// Execute the command

MyReader = MyCommand.ExecuteReader();

// ...

}

catch (Exception excep)

{

}

finally

{

if (MyReader != null)

{

MyReader.Close();

MyReader.Dispose();

}

if (MyConnection != null)

{

MyConnection.Close();

MyConnection.Dispose();

}

}