SQL Server 2008 New Features - Table-Value Parameter
page 1 of 1
Published: 10 Sep 2008
Abstract
With the ability to pass a table or a number of rows as parameter, SQL Server has reduced the repeated number of calls between your application components. Table-value parameters are going to change the way you work with SQL Server programming. Nidal examines this feature in detail with the help of relevant SQL statements along with step-by-step analysis and screenshots.
by Nidal Arabi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 17504/ 29

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.



User Comments

Title: Real World Stuff...   
Name: Bill Myers
Date: 2010-02-10 4:08:37 PM
Comment:
I like the article but there are so many things that we put on the internet that show an example but have no real value.

Like where would we pass in an ID column? Never. We never would. You would only use the IDENTITY(1, 1) to do this for you. How about an example with procedures that do not take in an identity column and in one execution use multiple procedures?

You are a great coder don't get me wrong but these types of examples are very hard to apply with a real life situation.

Could you please post an example where you are calling a stored procedure:

SPROC_INSSomething

EXEC SPROC_INSOtherThings
EXEC SPROC_INSManyThings

So 1 procedure calls two other procedures but they all use the data inside the table type that is created.

Like if you would do an AP record and a AR record for payables and receivables and then the GL true ups for the accounts.

Please give me your thoughts.

Thanks,

B
Title: Mr   
Name: Eugene
Date: 2009-07-27 4:56:28 AM
Comment:
Good example, but, multi-row insertion is often used out of php approach. The insertion syntax in stored procedure shows that multiple insertion can be done straight away without using table variable at all.
Title: Table Value parameter in SQL Server 2008   
Name: Nitin Sharma
Date: 2008-09-15 6:54:05 AM
Comment:
That seems to be gr8.........

Good article .
Thanks,
Nitin Sharma
Software Engineer
.Net Technologies
Title: Programmer   
Name: Shaikh Rais
Date: 2008-09-14 1:38:43 AM
Comment:
Is it possible to do in sql server 2005






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-03-30 10:39:14 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search