Introduction

Sometimes when developing, you may need to pass multiple rows of a table as a parameter to a stored procedure. Previously, we had to make several trips to insert multiple records. SQL Server 2008 wanted to make our life easier and created the option to pass Table-Value as a parameter.

About The Feature

This new feature allows the developer to create a Table variable type in MSSQL and use it in the same way you use an integer data type. You can pass this variable to any stored procedure and use its contents or even update its contents and return it back.

Requirements

In order to apply the article procedures, you should have installed any version of Microsoft SQL Server 2008 (Express, Standard, Developer, or Enterprise) and applied the first article of this series - SQL Server 2008 New Features (Row Constructors).

Step-by-Step Explanation

Follow the easy steps below to create your environment and work area.

1.    Open Microsoft SQL Server Management Studio.

2.    Right click the database DbWork and choose query window.

3.    Use the listing below to create an employee table as well as employee skill table.

Listing 1 - T-SQL to create the employee table as well as employee skills table

Create Table TblEmployee
(EmployeeId int primary key,
 EmployeeName varchar(50))
 
Create Table TblEmpSkills
(EmployeeId int,
 EmployeeSkill varchar(50),
 AcquiredSince date)

4.    Use the following listing below in order to insert some records in the employee table.

Listing 2 - T-SQL to insert data into the employee table using the row constructor method

Insert Into TblEmployee (EmployeeId, EmployeeName)
 Values (1,'Nidal Arabi'),
            (2,'Kamal Diab'),
            (3,'Hisham Takkoush'),
            (4,'Anis Khatib')

5.    The idea now is that each employee can have multiples skills attached to him. We are going to implement the solution using table-value parameter.

6.    We first need to create the table-value parameter. Listing 3 shows the creation of the SkillTable variable.

Listing 3 - T-SQL to create the SkillTable variable

Create Type  SkillTable as Table
(EmployeeId int, EmployeeSkill varchar(50))

7.    Now, we declare a variable of type SkillTable and fill it with data. Check Listing 4.

Listing 4 - T-SQL to create declare a variable of SkillTable and fill Data

Declare @EmpSkills as SkillTable
Insert Into @EmpSkills (EmployeeId, EmployeeSkill)
Values (1,'.NET'),
            (1,'SQL'),
            (2,'All'),
            (3,'Sales')

8.    Now, it is time to create a stored procedure that would use the EmpSkills as a passed variable. Listing 5 shows the use of this variable to bulk insert the skills using the variable. Please note the read only attribute next to the variable name prohibits the stored procedure from modifying the content of the table-value parameter.

Listing 5 - T-SQL Shows the complete usage of the data type table value

CREATE PROCEDURE AssociateSkills
    @SkillList SkillTable READONLY
AS 
    INSERT INTO TblEmpSkills (EmployeeID, EmployeeSkill, AcquiredSince)
      SELECT EmployeeId, EmployeeSkill, GETDATE() FROM  @SkillList;
 
Declare @EmpSkills as SkillTable
 
Insert Into @EmpSkills (EmployeeId, EmployeeSkill)
Values (1,'.NET'),
            (1,'SQL'),
            (2,'All'),
            (3,'Sales')
Execute AssociateSkills @EmpSkills

Figure 1

9.    Please respect the sequence in executing Listing 5.

Summary

In my humble opinion, the table value parameter would solve the problem of sending multiple row values and will also, to a certain extent, replace the temporary table because they can be sent between stored procedures.