Table-valued parameters - SQL Server 2008

May 21, 2008

In previous versions of SQL Server, it has not been possible to pass a table variable, as a parameter, to a stored procedure. Microsoft introduces table-valued parameters, along with other features, in SQL Server 2008.

Table-valued parameters have two major benefits:

Steps involved in creating and using Table-valued parameters:

Let’s create a database “TestDB” using the following DDL SQL Statements.

USE [master]
GO
/****** Object:  Database [TestDB]    
  Script Date: 05/13/2008 09:04:00 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')
DROP DATABASE TestDB
GO
Create database TestDB
go

Let’s create the table TestLocationTable using the following DDL SQL Statements.

USE [TestDB]
GO
/****** Object:  Table [dbo].[TestLocationTable]    Script Date: 05/13/2008 09:35:06 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestLocationTable]') AND type in (N'U'))
DROP TABLE [dbo].[TestLocationTable]
GO
USE [TestDB]
GO
/****** Object:  Table [dbo].[TestLocationTable]    Script Date: 05/13/2008 09:35:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TestLocationTable](
 [Id] [int] NULL,
 [shortname] [char](3) NULL,
 [name] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Let’s add data to the table that we created using the following DML SQL Statements.

USE [TestDB]
GO
insert into TestLocationTable ( Id, shortname, Name)  select 1, 'NA1', 'NewYork'
insert into TestLocationTable ( Id, shortname, Name)  select 2, 'NA2', 'NewYork'
insert into TestLocationTable ( Id, shortname, Name)  select 3, 'NA3', 'NewYork'
insert into TestLocationTable ( Id, shortname, Name)  select 4, 'EU1', 'London'
insert into TestLocationTable ( Id, shortname, Name)  select 5, 'EU2', 'London'
insert into TestLocationTable ( Id, shortname, Name)  select 6, 'AS1', 'Tokyo'
insert into TestLocationTable ( Id, shortname, Name)  select 7, 'AS2', 'HongKong'
go

Now let’s create a Table Type with a similar table structure as the table TestLocationTable shown below.

USE [TestDB]
GO
/****** Object:  UserDefinedTableType [dbo].[OfficeLocation_Tabetype]    Script Date: 05/13/2008 09:49:16 ******/
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id 
  WHERE st.name = N'OfficeLocation_Tabetype' AND ss.name = N'dbo')
DROP TYPE [dbo].[OfficeLocation_Tabetype]
GO
USE [TestDB]
GO
/****** Object:  UserDefinedTableType [dbo].[OfficeLocation_Tabetype]    Script Date: 05/13/2008 09:49:32 ******/
CREATE TYPE [dbo].[OfficeLocation_Tabetype] AS TABLE(
 [Id] [int] NULL,
 [shortname] [char](3) NULL,
 [name] [varchar](100) NULL
)
GO

Let’s create a procedure that will accept table type as a parameter.

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[usp_selectProdLocation]    Script Date: 05/13/2008 09:34:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_InsertProdLocation]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_selectProdLocation]
GO
CREATE PROCEDURE usp_InsertProdLocation
    @TVP OfficeLocation_Tabetype READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO TestLocationTable Select ID, shortname, name from @TVP
    where convert(varchar(10),id)+shortname+name not in (select
    convert(varchar(10),id)+shortname+name from TestLocationTable)
        
 GO

This procedure gets the table variable as input and inserts only the data that are not available in the TestLocationTable. Now, let’s try to create a table variable and execute the stored procedure usp_InsertProdLocation immedietly as shown below.

use TestDB
go
DECLARE @TV AS [OfficeLocation_Tabetype]
INSERT INTO @TV (Id, Shortname, Name)     SELECT 12, 'ME1', 'Dubai'
INSERT INTO @TV (Id, Shortname, Name)     SELECT 13, 'ME2', 'Tehran'
INSERT INTO @TV (Id, Shortname, Name)     SELECT 17, 'EA1', 'Bombay'
INSERT INTO @TV (Id, Shortname, Name)     SELECT 18, 'EA2', 'Karachi'
INSERT INTO @TV (Id, Shortname, Name)     SELECT  3, 'NA3', 'NewYork'
INSERT INTO @TV (Id, Shortname, Name)     SELECT  4, 'EU1', 'London'
   
exec usp_InsertProdLocation @TV
go

Now, let’s query all the data from the table TestLocationTable using the following Transact SQL statement.

use TestDB
go
select * from TestLocationTable
go

The result is shown below.

Result

Id, shortname, name
1, NA1, NewYork
2, NA2, NewYork
3, NA3, NewYork
4, EU1, London
5, EU2, London
6, AS1, Tokyo
7, AS2, HongKong
12, ME1, Dubai
13, ME2, Tehran
17, EA1, Bombay
18, EA2, Karachi
(11 row(s) affected)

From the results, you can see that the storedprocedure InsertProdLocation inserted all of the rows from the table variable @TV that didn’t match the rows in the TestLocationTable.

We could also pass the table variable to a function. Let’s create a simple function as shown below.

USE [TestDB]
GO
/****** Object:  UserDefinedFunction [dbo].[myfunction]    Script Date: 05/13/2008 13:15:11 ******/
IF  EXISTS (SELECT * FROM sys.objects 
  WHERE object_id = OBJECT_ID(N'[dbo].[myfunction]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[myfunction]
GO
create function dbo.myfunction (@TV OfficeLocation_Tabetype READONLY)
returns int
as
begin
declare @i int
set @i=(Select COUNT(*) from @TV)
return @i
end

Now let’s call the created function by creating a table variable and passing the variable as a parameter to the function as shown below.

USE [TestDB]
GO
DECLARE @TV AS [OfficeLocation_Tabetype]
INSERT INTO @TV (Id, Shortname, Name)     SELECT 12,'ME1','Dubai'
INSERT INTO @TV (Id, Shortname, Name)     SELECT 13,'ME2','Tehran'
INSERT INTO @TV (Id, Shortname, Name)     SELECT 17,'EA1','Bombay'
INSERT INTO @TV (Id, Shortname, Name)     SELECT 18,'EA2','Karachi'
INSERT INTO @TV (Id, Shortname, Name)     SELECT  3,'NA3','NewYork'
INSERT INTO @TV (Id, Shortname, Name)     SELECT  4,'EU1','London'
select dbo.myfunction(@TV)
go

Results

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
-----------
6

Note: All these scripts are written and tested on SQL Server 2008 CTP6.

Conclusion

This article has illustrated the function and usage of Table-valued parameters.

» See All Articles by Columnist MAK