表值參數有兩大優點：一是它不需要為初始的數據加鎖，二是它不會導致語句重新編譯。
表值參數的創建和使用包括以下步驟：
1) 創建表類型
2) 創建一個可將表類型作為參數來接受的存儲過程或函數
3) 創建表變量並插入數據
4) 調用該存儲過程和函數，並將表變量作為參數傳遞。
下面，我們來一步步分解這個創建和使用的過程。首先，我們用以下的DDL SQL語句來創建一個名為“TestDB”的測試數據庫：
USE[master]
GO
IFEXISTS(SELECTnameFROMsys.databasesWHEREname=N'TestDB')
DROPDATABASETestDB
GO
CreatedatabaseTestDB
go
接下來我們使用以下的DDL SQL語句來創建一個名為TestLocationTable的表：
USE[TestDB]
GO
IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[TestLocationTable]')ANDtypein(N'U'))
DROPTABLE[dbo].[TestLocationTable]
GO
USE[TestDB]
GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
SETANSI_PADDINGON
GO
CREATETABLE[dbo].[TestLocationTable](
[Id][int]NULL,
[shortname][char](3)NULL,
[name][varchar](100)NULL
)ON[PRIMARY]
GO
SETANSI_PADDINGOFF
GO
然後，使用以下的DML SQL語句將數據添加到我們上面創建的表中：
USE[TestDB]
GO
insertintoTestLocationTable(Id,shortname,Name)select1,'NA1','NewYork'
insertintoTestLocationTable(Id,shortname,Name)select2,'NA2','NewYork'
insertintoTestLocationTable(Id,shortname,Name)select3,'NA3','NewYork'
insertintoTestLocationTable(Id,shortname,Name)select4,'EU1','London'
insertintoTestLocationTable(Id,shortname,Name)select5,'EU2','London'
insertintoTestLocationTable(Id,shortname,Name)select6,'AS1','Tokyo'
insertintoTestLocationTable(Id,shortname,Name)select7,'AS2','HongKong'
go
下一步，我們要創建一個和TestLocationTable表具有相似表結構的表類型(TABLE TYPE)，語句如下：
USE[TestDB]
GO
IFEXISTS(SELECT*FROMsys.typesstJOINsys.schemasssONst.schema_id=ss.schema_id
WHEREst.name=N'OfficeLocation_Tabetype'ANDss.name=N'dbo')
DROPTYPE[dbo].[OfficeLocation_Tabetype]
GO
USE[TestDB]
GO
CREATETYPE[dbo].[OfficeLocation_Tabetype]ASTABLE(
[Id][int]NULL,
[shortname][char](3)NULL,
[name][varchar](100)NULL
)
GO
緊接著，我們要創建一個可以將表類型作為一個參數來接受的存儲過程，使用的語句如下：
USE[TestDB]
GO
IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[usp_InsertProdLocation]')ANDtypein(N'P',N'PC'))
DROPPROCEDURE[dbo].[usp_selectProdLocation]
GO
CREATEPROCEDUREusp_InsertProdLocation
@TVPOfficeLocation_TabetypeREADONLY
AS
SETNOCOUNTON
INSERTINTOTestLocationTableSelectID,shortname,namefrom@TVP
whereconvert(varchar(10),id)+shortname+namenotin(select
convert(varchar(10),id)+shortname+namefromTestLocationTable)
GO
這個存儲過程將表變量作為導入值接收，並且只插入TestLocationTable中沒有的數據。現在，我們可以嘗試創建一個表變量，並執行上面創建的存儲過程usp_InsertProdLocation，語句如下：
useTestDB
go
DECLARE@TVAS[OfficeLocation_Tabetype]
INSERTINTO@TV(Id,Shortname,Name)SELECT12,'ME1','Dubai'
INSERTINTO@TV(Id,Shortname,Name)SELECT13,'ME2','Tehran'
INSERTINTO@TV(Id,Shortname,Name)SELECT17,'EA1','Bombay'
INSERTINTO@TV(Id,Shortname,Name)SELECT18,'EA2','Karachi'
INSERTINTO@TV(Id,Shortname,Name)SELECT3,'NA3','NewYork'
INSERTINTO@TV(Id,Shortname,Name)SELECT4,'EU1','London'
execusp_InsertProdLocation@TV
go
這時候，我們可以使用以下的TSQL語句從表TestLocationTable查詢所有的數據：
useTestDB
go
select*fromTestLocationTable
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
(11row(s)affected)
從返回的結果，我們可以看到存儲過程usp_InsertProdLocation 插入了表變量@TV中和表TestLocationTable所有不匹配的行。
我們還可以將表變量傳遞給一個函數。下面我們創建一個簡單的函數，語句如下：
USE[TestDB]
GO
IFEXISTS(SELECT*FROMsys.objects
WHEREobject_id=OBJECT_ID(N'[dbo].[myfunction]')ANDtypein(N'FN',N'IF',N'TF',N'FS',N'FT'))
DROPFUNCTION[dbo].[myfunction]
GO
createfunctiondbo.myfunction(@TVOfficeLocation_TabetypeREADONLY)
returnsint
as
begin
declare@iint
set@i=(SelectCOUNT(*)from@TV)
return@i
end
現在，我們通過創建一個表變量並將該變量作為一個參數傳遞給已創建的函數以調用該函數，語句如下：
USE[TestDB]
GO
DECLARE@TVAS[OfficeLocation_Tabetype]
INSERTINTO@TV(Id,Shortname,Name)SELECT12,'ME1','Dubai'
INSERTINTO@TV(Id,Shortname,Name)SELECT13,'ME2','Tehran'
INSERTINTO@TV(Id,Shortname,Name)SELECT17,'EA1','Bombay'
INSERTINTO@TV(Id,Shortname,Name)SELECT18,'EA2','Karachi'
INSERTINTO@TV(Id,Shortname,Name)SELECT3,'NA3','NewYork'
INSERTINTO@TV(Id,Shortname,Name)SELECT4,'EU1','London'
selectdbo.myfunction(@TV)
go
執行結果如下：
(1row(s)affected)
(1row(s)affected)
(1row(s)affected)
(1row(s)affected)
(1row(s)affected)
(1row(s)affected)
-----------
6
注：上面所演示的腳本都是在SQL Server 2008 CTP6版本上進行編寫並經過測試的。
