Passing multiple rows to a stored procedure

Passing multiple rows to a stored procedure

Passing multiple rows to a sp has been quite a problem for a while in sql server. Although many workarounds such as using an XML data type can be used, it can be quite cumbersome. With SQL 2008 this burden has been mitigated by the introduction of table-valued parameters. It can also interface with .net applications as you will see later on in this article.

Using Table-Valued Parameters

First and foremost we will have to create a user-defined table type.

Database Node > Programmability > Types > User-Defined Table Types

Script to create a User-Defined Table type

--Create User-defined Table Type

CREATE TYPE dbo.EmployeeRecords AS TABLE

(

id int PRIMARY KEY,

FirstName varchar(20) NOT NULL,

LastName varchar(20) NOT NULL,

HireDate datetime NOT NULL DEFAULT GETDATE()

)

GO

--Using the User-Defined Table Type

DECLARE @MyEmployeeRecords dbo.EmployeeRecords

INSERT INTO @MyEmployeeRecords(id ,FirstName,LastName,HireDate)

VALUES (1112,'Anne','Smith','2009-09-30 10:00:00'),

(2222,'John','Hemir','2009-09-30 10:00:00')

-- Select the inserted records using new type

SELECT * FROM @MyEmployeeRecords

Using a user-defined table type as a parameter in a stored procedure

1. Create the user-defined table type if you haven’t already done so

--Create User-defined Table Type

CREATE TYPE dbo.EmployeeRecords AS TABLE

(

id int PRIMARY KEY,

FirstName varchar(20) NOT NULL,

LastName varchar(20) NOT NULL,

HireDate datetime NOT NULL DEFAULT GETDATE()

)

GO

2. Grant permission to the user to use the user-defined table type

GRANT EXECUTE ON TYPE::dbo.EmployeeRecords TO ;

3. Create the stored procedure

a. Create the table to which data will be added

CREATE TABLE dbo.Employee

(

id int PRIMARY KEY,

FirstName varchar(20) NOT NULL,

LastName varchar(20) NOT NULL,

HireDate datetime NOT NULL DEFAULT GETDATE()

)

b. Write the sp

CREATE PROCEDURE InsertEmployee

@MyParameter EmployeeRecords READONLY

AS

INSERT INTO Employee (id ,FirstName,LastName,HireDate)

SELECT id,FirstName,LastName,HireDate

FROM @MyParameter

-- for the table valued parameter

 GO

c. Executing the SP

--Using the User-Defined Table Type in stored procedure

DECLARE @MyEmployeeRecords EmployeeRecords

INSERT INTO @MyEmployeeRecords(id ,FirstName,LastName,HireDate)

VALUES (1111,'Anne','Smith','2009-09-30 10:00:00'),

(2222,'John','Hemir','2009-09-30 10:00:00')

EXEC InsertEmployee @MyEmployeeRecords

-- Select the records inserted using Stored procedure

SELECT * FROM Employee

Simple, isn’t it? So next time you think about passing multiple rows try this out, it is a lot easier and would save you some programming time as well.

Now let’s see as to how we can use this in a .net application.

Table-Valued parameters with ADO.NET

Table-valued parameters facilitate developers to send many rows of data to the database in a single trip thereby saving network resources and increasing performance. As of now you cannot return table-valued parameters, they are only available in one flavor, Input only. It is also worth making a note that these variables are strongly typed and their structure is automatically validated. Once all the rows are bundled into it and it is passed to the SQL server it can be used in any transact-SQL statement like a conventional table.

Drawbacks of table-valued parameters

1. Can’t use ALTER TABLE statements to modify its design

2. In transact-sql these parameters are read only. Data manipulation of any kind is not possible. If parameters which are passed into SP’s need alteration then they need to be inserted into a table variable or temporary variable.

3. The cannot be passed to user-defined functions

4. Can only be indexed to support PK and Unique constraints

Modifying data with table-valued parameters

Following are few examples as to how one may go about using these variables in data manipulation.

Updating data

UPDATE dbo.Employee

    SET Employee.Branch = e.Branch

    FROM dbo.Employee INNER JOIN @MyEmployeeRecords AS e

    ON dbo.Employee.id = e.id;

Inserting Data

 INSERT INTO Employee (id ,FirstName,LastName,HireDate)

 SELECT id,FirstName,LastName,HireDate

 FROM @MyEmployeeRecords

Passing Table-Valued parameters to SP’s

Let’s consider the following application where data in a table is updated using a table-valued parameter.

Updating Employee Information

The following application displays employee information in a grid and also facilitates to update all rows by the way of a table-valued parameter.

The following code snippet has a command object with a table-valued parameter whose type is set to “structured”. This parameter carries the table to the db layer. In this case the variable “updatedEmployees” is of type datatable.

// Configure the SqlCommand and SqlParameter.

SqlCommand updateCommand = new SqlCommand("UpdateEmployee", conn);

conn.Open();

updateCommand.CommandType = CommandType.StoredProcedure;

SqlParameter tvpParam = updateCommand.Parameters.AddWithValue(

"@MyParameter", updatedEmployees);

tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.

updateCommand.ExecuteNonQuery();

I have also included the code sample I have been working on in this article. Hope this will be useful and helpful. Happy coding to all.