Table-Value Parameter in Storedprocedure - SQL SERVER -2008

Pass multiple rows of a table as a parameter to a stored procedure.

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.


How we use?:


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,'Asha Patel'),
(2,'Kamal Chaurasia'),
(3,'Tushar Jadhav'),
(4,'Kiran Patel')

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,'VB'),
(3,'Dot Net')
Execute AssociateSkills @EmpSkills


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.