SQL Server 2008:表值参数的创建和使用

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