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
- About table-valued parameter at Microsoft Docs.