Table-valued parameters – SQL Server 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:

a.  It does not
acquire locks for the initial data

b.  It does not cause
the statement to recompile

Steps involved in creating and using Table-valued parameters:

a.  Create table Type

b.  Create a stored
proc or function that will accept the table type as a parameter

c.  Create a table
variable and insert data

d.  Call the proc and
function and pass the table variables as parameter.

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles