In my job, I was I found I needed to pass an array of IDs to an Stored Procedure in order to associate all of them in a single hit. The solution was rather simple. Follow me

Receive Array Parameters In a Stored Procedure In Sql Server

Hello everybody again.

Why?

Why would I want to pass an array as parameter to an SQL Server Stored Procedure? Well, because it can help you avoid several trips to the server!

We'll invent a quick example. Imagine this. In your DB Schema, you've got a table of Managers, and a table of Employees. Because of business rules, each employee can have many managers, and of course each manager has quite a bit of employees. You then create an intermediate relational table which stores one Id of a manager and one Id of an employee in each row representing this relation.

Now, in you app you've set an interface that let's you select a list of employees to assign in one single click to a manager. Your process gets a list (array, collection, whatever) of employee Ids. Well, one thing you could do is send several insert statements to the server in one transaction. This I don't like it. I want to realize a single operation, I don't know that's just the way I like it.

So, after investigating a little I was able to come up wih a solution. Why can't I declare my SP to receive the array containing the employee Ids. Wait, I can!

How?

Receiving "arrays" as parameters is a stored procedure in SQL Server 2008+ is quite simple. You just first have to remember that SQL already uses a concept similar to an array. Yes, the table.

You can easily imagine an array as a deformed table (a table which has only one column). Having this in mind let's jump to our quest... in which our first step is...

1. Create a new table Type to use as type for our array

Fire up SQL Server and execute the following command on your database:

CREATE TYPE [dbo].[IntIDs] AS TABLE(
    [IntID] [int]
)

This will indeed create a new type called IntIDs which is a table structure (with all its privileges) with one row of type int.

2. Declare your stored procedures to accept a parameter of the new type

Ok, according to the original example, we need to insert in a table to relate one ManagerId to serveral EmployeeId's. We'll receive in our SP a parameter of type IntIDs containing all the EmployeeId's. I'm sure you'll understand the code:

CREATE PROCEDURE [dbo].[AddEmployeesToManager] ( @ManagerId int, @EmployeeIds AS IntIDs READONLY ) AS BEGIN SET NOCOUNT ON;

INSERT INTO dbo.EmployeesInManager(ManagerId, EmployeeId)
(SELECT @ManagerId, I.IntID FROM @EmployeeIds I)

END

And.. that's all! Really! See how I can treat my table parameter just as any other table?

3. Invoke your SP from C# ADO

This is an easy one, too. The only thing you have to do to send your parameter is to send it as an ADO DataTable. Yup, that's all. Let's see a simple example, I hope you follow it well.

var managerId = 11;
var employeeIds = new [] {1, 2, 4, 5}; //our collection

//but we need to send it as a table to our SP!
var intIds = new DataTable();
intIds.Columns.Add("IntId", typeof(int));
foreach(var eId in employeeIds) intIds.Rows.Add(eId);

//assume we already have an open connection "conn"
// Prepare a call to the stored procedure
SqlCommand c = new SqlCommand( "dbo.AddEmployeesToManager", conn);
c.Parameters.AddWithValue("@ManagerId", managerId);
c.Parameters.AddWithValue("@EmployeeIds", intIds);

c.ExecuteNonQuery();

That's it!! Remember you cna use your parameter as if it was any table, letting you do joins and aggregation. Enjoy!

Posted by: fabzter
Last revised: 30 Apr, 2013 01:23 PM History

Comments

Your Comments

Used for your gravatar. Not required. Will not be public.
Posting code? Indent it by four spaces to make it look nice. Learn more about Markdown.

Preview