How To Pass Array Or List To Stored Procedure

Frequently I have come across requirements to send list or array of data to a stored procedure through its input parameter. There are several ways to do this. While using older versions of SQL Server, I’ve used to the XML method to pass array or list to stored procedure. In the latest versions of SQL Server, we can use the User Defined Data Type (UDT) with a base type of table to send array or list through a parameter. Let’s see with an example how to use User Defined Type to pass a list or an array to a stored procedure.

In this example, I will be passing a list of Customer IDs and Customer Names to the stored procedure. To start with create a UDT with the base type of table. i.e. User Defined table Type. The table structure should be similar to the list you are trying to send.

Creating An User Defined Table Type

Here is the script for creating User Defined Table Type.

/* Create User Defined Table Type named udtCustomers */
CREATE TYPE udtCustomers AS TABLE
    (
		CustomerID INT,
		CustomerName VARCHAR(50)
    )
GO

Create The Stored Procedure

Create a stored procedure with the above created UDT as a parameter. This is otherwise called as table-valued parameter.

/* Create Stored Procedure */
CREATE PROCEDURE dbo.GetCustomers
  @udt AS dbo.udtCustomers READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT Cast(CustomerID as varchar(5)) + ': ' + CustomerName FROM @udt;
END
GO

Execute The Stored Procedure

Before executing the stored procedure, declare a variable for the UDT and insert some values. Then pass the variable to the stored procedure while executing it.

Declare @CustomerList as dbo.udtCustomers
INSERT INTO @CustomerList VALUES (1, 'John')
INSERT INTO @CustomerList VALUES (2, 'Mark')
INSERT INTO @CustomerList VALUES (3, 'Stacy')
INSERT INTO @CustomerList VALUES (4, 'Peter')
INSERT INTO @CustomerList VALUES (5, 'Jane')

Exec dbo.GetCustomers @udt = @CustomerList

/* Result */
--------------
1: John
2: Mark
3: Stacy
4: Peter
5: Jane

Pass Array Or List To Stored Procedure From C#

Now to pass an array or a list to this stored procedure from the application. For this example, I am using C# application to execute the stored procedure. In this example an array of data in the form of datatable.

DataTable dt = new DataTable();
dt.Columns.Add('CustomerID');
dt.Columns.Add('CustomerName');
dt.Rows.Add(1, 'John');
dt.Rows.Add(2, 'Mark');
dt.Rows.Add(3, 'Stacy');
 
SqlConnection con = new SqlConnection('Data Source=MACWINDOWS;' +
    'integrated Security=SSPI;initial catalog=Test.MyTecBits.com;');
SqlCommand cmd = new SqlCommand('GetCustomers', con);
 
cmd.CommandType = CommandType.StoredProcedure;
 
SqlParameter IdIn = cmd.Parameters.AddWithValue('@udt', dt);
IdIn.Direction = ParameterDirection.Input;
 
con.Open();
 
SqlDataReader myReader = cmd.ExecuteReader();
while (myReader.Read())
{
    txtResult.Text += myReader.GetString(0) + System.Environment.NewLine;
};
myReader.Close();

Reference

Related Article


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.