在SQL Server 2005或更早的版本中的數據庫中，表變量是不能作為存儲過程的參數的。當多行數據到SQL Server需要發送多行數據到SQL Server ，開發者要麼每次發送一列記錄，或想出其他的變通方法，以滿足需求。雖然在.Net 2.0中提供了個SQLBulkCopy對象能夠將多個數據行一次性傳送給SQL Server，但是多行數據仍然無法一次性傳給存儲過程。
SQL Server 2008中的T-SQL功能新增了表值參數。利用這個新增特性，我們可以很方便地通過T-SQL語句，或者通過一個應用程序，將一個表作為參數傳給存儲過程。
1、用戶自定義表類型
當第一次看看新的表值參數，我認為使用此功能有點復雜。有幾個步驟。要做的第一件事是定義表型。在Management Studio 2008中的“Programmability”“Type”節點，您可以看到“User-Defined Table Types(用戶自定義表類型)”，如圖1所示 。
圖 1：用戶自定義表類型
點擊右鍵，在彈出菜單中選擇“新用戶定義的表型... ” ，會新建一個模板中的查詢窗口，如圖2所示 。
在填寫在適當的數值之後，點擊確定按鈕，一個“CREATE TYPE”的聲明取代了范本。這時，你也可以手動增加一些列，或者增加一些限制條件，最後點擊確定按鈕。
以下是最終的代碼：
以下為引用的內容：
-- ================================
-- Create User-defined Table Type
-- ================================
USE Test
GO
-- Create the data type
CREATE TYPE dbo.MyType AS TABLE
(
col1 int NOT NULL,
col2 varchar(20) NULL,
col3 datetime NULL,
PRIMARY KEY (col1)
)
GO
在運行代碼之後，對象的定義就建立好了，你可以在“User-Defined Table Type（用戶自定義表類型”中查看屬性，如圖4所示，但沒法修改它們。如果要修改的類型，你只能將其刪除，然後按照修改後的屬性再次創建它。
2、使用用戶自定義的表類型
如果打算在T-SQL代碼中使用，您還必須創建一個新類型的變量，然後將具體的表的名稱賦值給該變量。一旦賦值後，您可以在其他的T-SQL語句中使用它。因為它是一個變量，在批處理完成後，它也自動失效，結束生命周期。
請注意下面的代碼，MyType是我們之前剛剛創建的數據類型。
以下為引用的內容：
DECLARE @MyTable MyType
INSERT INTO @MyTable(col1,col2,col3)
VALUES (1,'abc','1/1/2000'),
(2,'def','1/1/2001'),
(3,'ghi','1/1/2002'),
(4,'jkl','1/1/2003'),
(5,'mno','1/1/2004')
SELECT * FROM @MyTable
在變量的有效范圍內，你可以象操作正常的表一樣來操作這個變量，如與另一個表象關聯或者將變量中的記錄填充到另一個表。對於表變量來說，你無法修改表定義。
正如前面提到的，變量不能超出它的有效的范圍。如果T-SQL腳本由多個批處理組成，變量只有在批處理內才能創建並有效使用。
3、使用變量作為參數
到目前為止，我們還沒有看到經常表變量無法實現的功能。其好處是能夠將變量作為參數傳給存儲過程。當然一個存儲過程必須先建立，使用新的類型作為其中的一個參數。
下面這個例子，通過代碼創建一個常規表，並對其填充記錄。
以下為引用的內容：
USE [Test]
GO
CREATE TABLE [dbo].[MyTable] (
[col1] [int] NOT NULL PRIMARY KEY,
[col2] [varchar](20) NULL,
[col3] [datetime] NULL,
[UserID] [varchar] (20) NOT NULL
)
GO
CREATE PROC usp_AddRowsToMyTable @MyTableParam MyType READONLY,
@UserID varchar(20) AS
INSERT INTO MyTable([col1],[col2],[col3],[UserID])
SELECT [col1],[col2],[col3],@UserID
FROM @MyTableParam
GO
請注意表值參數後面帶了個READONLY參數。這是必需的，不能在例程體中對表值參數執行諸如 UPDATE、DELETE 或 INSERT 這樣的 DML 操作。
最後，我們對創建表值變量，對變量進行賦值，並調用存儲過程。
以下為引用的內容：
DECLARE @MyTable MyType
INSERT INTO @MyTable(col1,col2,col3)
VALUES (1,'abc','1/1/2000'),
(2,'def','1/1/2001'),
(3,'ghi','1/1/2002'),
(4,'jkl','1/1/2003'),
(5,'mno','1/1/2004')
EXEC usp_AddRowsToMyTable @MyTableParam = @MyTable, @UserID = 'Kathi'
SELECT * FROM MyTable
為了讓用戶使用自定義表類型，執行或控制權限必須是理所當然的。以下是授權命令：
以下為引用的內容：
GRANT EXECUTE ON TYPE::dbo.MyType TO TestUser;
4、通過.Net應用程序調用
表值參數這一特性最大的亮點在於可以在.net應用中使用表值參數。為了做到這一點，你必須要先安裝.Net 3.5框架，並確保應用程序中已經引用了 System.Data.SqlClIEnt命名空間。創建表值參數時需要用到一些新的SQL數據類型（如DataTable、DataColumn等）。
首先創建一個本地數據表，並插入一些記錄。肯定的是， DataTable中創建符合用戶定義的表型的列計數和數據類型。
以下為引用的內容：
'Create a local table
Dim table As New DataTable("temp")
Dim col1 As New DataColumn("col1", System.Type.GetType("System.Int32"))
Dim col2 As New DataColumn("col2", System.Type.GetType("System.String"))
Dim col3 As New DataColumn("col3", System.Type.GetType("System.DateTime"))
table.Columns.Add(col1)
table.Columns.Add(col2)
table.Columns.Add(col3)
'Populate the table
For i As Integer = 20 To 30
Dim vals(2) As Object
vals(0) = i
vals(1) = Chr(i + 90)
vals(2) = System.DateTime.Now
table.Rows.Add(vals)
Next
我們在代碼中采用存儲過程：創建一個命令對象，並新增兩個參數。代碼如下圖所示：
以下為引用的內容：
'Create a command object that calls the stored proc
Dim command As New SqlCommand("usp_AddRowsToMyTable", conn)
command.CommandType = CommandType.StoredProcedure
'Create a parameter using the new type
Dim param As SqlParameter = command.Parameters.Add
("@MyTableParam", SqlDbType.Structured)
command.Parameters.AddWithValue("@UserID", "Kathi")
請注意@ MyTableParam參數的數據類型(SqlDbType.Structured)，這是.Net 3.5中新增的功能。最後，將當地表賦值給表值參數，並執行該命令。
以下為引用的內容：
'Set the value of the parameter
param.Value = table
'Execute the query
command.ExecuteNonQuery()
5、小結
SQL Server 2008中新增的表值參數特性，減少了應用程序與SQL Server數據庫服務器之間的交互，提升了程序性能。
