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.
