中文字幕免费精品_亚洲视频自拍_亚洲综合国产激情另类一区_色综合咪咪久久

表值參數(Table-valued parameter)是SQL Server 2008的一個新特性，在以前的版本中，沒有辦法把表變量當作一個參數傳遞給存儲過程。微軟在SQL Server2008中引入了表值參數的特性，可以實現這項功能。

表值參數有兩大優點：一是它不需要為初始的數據加鎖，二是它不會導致語句重新編譯。

表值參數的創建和使用包括以下步驟：

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

接下來我們使用以下的DDL SQL語句來創建一個名為TestLocationTable的表：

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

然后，使用以下的DML SQL語句將數據添加到我們上面創建的表中：

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

下一步，我們要創建一個和TestLocationTable表具有相似表結構的表類型(TABLE TYPE)，語句如下：

　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

這個存儲過程將表變量作為導入值接收，并且只插入TestLocationTable中沒有的數據。現在，我們可以嘗試創建一個表變量，并執行上面創建的存儲過程usp_InsertProdLocation，語句如下：

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

這時候，我們可以使用以下的TSQL語句從表TestLocationTable查詢所有的數據：

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)

從返回的結果，我們可以看到存儲過程usp_InsertProdLocation 插入了表變量@TV中和表TestLocationTable所有不匹配的行。

我們還可以將表變量傳遞給一個函數。下面我們創建一個簡單的函數，語句如下：

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

注：上面所演示的腳本都是在SQL Server 2008 CTP6版本上進行編寫并經過測試的。

更多信息請查看IT技術專欄