分类: 数据库开发技术
2008-05-28 08:40:08
表值参数有两大优点:一是它不需要为初始的数据加锁,二是它不会导致语句重新编译。
表值参数的创建和使用包括以下步骤:
1) 创建表类型
2) 创建一个可将表类型作为参数来接受的存储过程或函数
3) 创建表变量并插入数据
4) 调用该存储过程和函数,并将表变量作为参数传递。
下面,我们来一步步分解这个创建和使用的过程。首先,我们用以下的DDL SQL语句来创建一个名为“TestDB”的测试数据库:
USE [master] GO IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB') DROP DATABASE TestDB GO Create database TestDB go |
USE [TestDB] GO 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 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 |
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 |
USE [TestDB] GO 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 CREATE TYPE [dbo].[OfficeLocation_Tabetype] AS TABLE( [Id] [int] NULL, [shortname] [char](3) NULL, [name] [varchar](100) NULL ) GO |
紧接着,我们要创建一个可以将表类型作为一个参数来接受的存储过程,使用的语句如下:
USE [TestDB] GO 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 |
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 |
use TestDB go select * from TestLocationTable go |
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) |
USE [TestDB] GO 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 |
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 |
(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 |