許多人一直希望能夠?qū)崿F(xiàn)把表格變量傳遞到存儲過程中，如果變量可以被聲明，那么它就應(yīng)該能夠被傳遞。而最新的SQL Server 2008則有這項功能!想知道如何才能把表格變量(包括內(nèi)含的數(shù)據(jù))傳遞到存儲過程和功能中去嗎?
為什么要傳遞表值參數(shù)?
用戶常常會碰到許多需要把數(shù)值容器而非單個數(shù)值放到存儲過程里的情況。對于大部分的編程語言而言，把容器數(shù)據(jù)結(jié)構(gòu)傳遞到例程里或傳遞出來是很常見而且很必要的功能。TSQL也不例外。
SQL Server 2000通過OPENXML可以實現(xiàn)這個功能，用戶可以把數(shù)據(jù)存儲為VARCHAR數(shù)據(jù)類型然后進(jìn)行傳遞。到了SQL Server 2005，隨著 XML數(shù)據(jù)類型以及XQuery的出現(xiàn)，這個功能變得容易一點。但用戶仍然需要對XML數(shù)據(jù)進(jìn)行組建和粉碎才能夠使用它，因此這個功能使用起來并不簡單。SQL Server 2008則能夠把表值數(shù)據(jù)類型傳遞到存儲過程和功能中，從而大大地簡化了編程的工作，因為程序員無需再花心思去組建和解析XML數(shù)據(jù)了。該功能還可以讓客戶方開發(fā)員傳遞客戶方數(shù)據(jù)表格到數(shù)據(jù)庫中。
如何傳遞表格參數(shù)?
以銷售為例，首先建立一個 my SalesHistory表格，里面包含了產(chǎn)品銷售的信息。寫以下腳本就可以在數(shù)據(jù)庫里創(chuàng)建你選擇的表格：
IF OBJECT_ID('SalesHistory')>0
DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO
建立表值參數(shù)第一步是創(chuàng)建確切的表格類型，這一步非常重要，因為這樣你就可以在數(shù)據(jù)庫引擎里定義表格的結(jié)構(gòu)，讓你可以在需要的時候在過程代碼里使用該表格。下面的代碼創(chuàng)建 SalesHistoryTableType 表格類型定義：
CREATE TYPE SalesHistoryTableType AS TABLE
(
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO
如果想要查看系統(tǒng)里其他類型的表格類型定義，你可以執(zhí)行下面這個查詢命令，查看系統(tǒng)目錄：
SELECT * FROM sys.table_types
我們需要定義用來處理表值參數(shù)的存儲過程。下面這個程序能夠接受指定SalesHistoryTableType類型的表值參數(shù)，并加載到SalesHistory中，表值參數(shù)在Product列中的值為“BigScreen”：
CREATE PROCEDURE usp_InsertBigScreenProducts
(
@TableVariable SalesHistoryTableType READONLY
)
AS
BEGIN
INSERT INTO SalesHistory
(
Product, SaleDate, SalePrice
)
SELECT
Product, SaleDate, SalePrice
FROM
@TableVariable
WHERE
Product = 'BigScreen'
END
GO
傳遞的表格變量還可以用做任何其他表格的查詢數(shù)據(jù)。
在傳遞表值變量到程序中時必須使用 READONLY從句。表格變量里的數(shù)據(jù)不能做修改——除了修改你可以把數(shù)據(jù)用于任何其他的操作。另外，你也不能把表格變量用做OUTPUT參數(shù)——只能用做input參數(shù)。
使用自己的新表格變量類型
首先，要聲明一個變量類型SalesHistoryTableType，不需要再一次定義表格結(jié)構(gòu)，因為在創(chuàng)建這個表格類型的時候已經(jīng)定義過了。
DECLARE @DataTable AS SalesHistoryTableType
The following script adds 1,000 records into my @DataTable table variable:
DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=1000)
BEGIN
INSERT INTO @DataTable(Product, SaleDate, SalePrice)
VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))
INSERT INTO @DataTable(Product, SaleDate, SalePrice)
VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))
INSERT INTO @DataTable(Product, SaleDate, SalePrice)
VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))
SET @i = @i + 1
END
只要把數(shù)據(jù)加載到表格變量里，就可以把結(jié)構(gòu)傳遞到存儲過程中。
注意：當(dāng)表格變量作為參數(shù)傳遞后，表格會在存儲在tempdb系統(tǒng)數(shù)據(jù)庫里，而不是傳遞整個數(shù)據(jù)集在內(nèi)存里。因為這樣保證高效處理大批量數(shù)據(jù)。所有服務(wù)器方的表格變量參數(shù)傳遞都是通過使用reference調(diào)用tempdb中的表格。
EXECUTE usp_InsertBigScreenProducts
@TableVariable = @DataTable
想要查詢程序是否和預(yù)想效果一樣，可以執(zhí)行以下查詢來看記錄是否已經(jīng)插入到 SalesHistory表格中：
SELECT * FROM SalesHistory
總結(jié)：
雖然SQL Server 2008的參數(shù)傳遞功能的使用還有一些小小的局限性，比如不能修改參數(shù)中的數(shù)據(jù)和把變量用于output，但是它大大提高了程序性能，它可以減少server往返旅程數(shù)、利用表格限制并擴(kuò)展編程在數(shù)據(jù)庫引擎中的功能。
為什么要傳遞表值參數(shù)?
用戶常常會碰到許多需要把數(shù)值容器而非單個數(shù)值放到存儲過程里的情況。對于大部分的編程語言而言，把容器數(shù)據(jù)結(jié)構(gòu)傳遞到例程里或傳遞出來是很常見而且很必要的功能。TSQL也不例外。
SQL Server 2000通過OPENXML可以實現(xiàn)這個功能，用戶可以把數(shù)據(jù)存儲為VARCHAR數(shù)據(jù)類型然后進(jìn)行傳遞。到了SQL Server 2005，隨著 XML數(shù)據(jù)類型以及XQuery的出現(xiàn)，這個功能變得容易一點。但用戶仍然需要對XML數(shù)據(jù)進(jìn)行組建和粉碎才能夠使用它，因此這個功能使用起來并不簡單。SQL Server 2008則能夠把表值數(shù)據(jù)類型傳遞到存儲過程和功能中，從而大大地簡化了編程的工作，因為程序員無需再花心思去組建和解析XML數(shù)據(jù)了。該功能還可以讓客戶方開發(fā)員傳遞客戶方數(shù)據(jù)表格到數(shù)據(jù)庫中。
如何傳遞表格參數(shù)?
以銷售為例，首先建立一個 my SalesHistory表格，里面包含了產(chǎn)品銷售的信息。寫以下腳本就可以在數(shù)據(jù)庫里創(chuàng)建你選擇的表格：
IF OBJECT_ID('SalesHistory')>0
DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO
建立表值參數(shù)第一步是創(chuàng)建確切的表格類型，這一步非常重要，因為這樣你就可以在數(shù)據(jù)庫引擎里定義表格的結(jié)構(gòu)，讓你可以在需要的時候在過程代碼里使用該表格。下面的代碼創(chuàng)建 SalesHistoryTableType 表格類型定義：
CREATE TYPE SalesHistoryTableType AS TABLE
(
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO
如果想要查看系統(tǒng)里其他類型的表格類型定義，你可以執(zhí)行下面這個查詢命令，查看系統(tǒng)目錄：
SELECT * FROM sys.table_types
我們需要定義用來處理表值參數(shù)的存儲過程。下面這個程序能夠接受指定SalesHistoryTableType類型的表值參數(shù)，并加載到SalesHistory中，表值參數(shù)在Product列中的值為“BigScreen”：
CREATE PROCEDURE usp_InsertBigScreenProducts
(
@TableVariable SalesHistoryTableType READONLY
)
AS
BEGIN
INSERT INTO SalesHistory
(
Product, SaleDate, SalePrice
)
SELECT
Product, SaleDate, SalePrice
FROM
@TableVariable
WHERE
Product = 'BigScreen'
END
GO
傳遞的表格變量還可以用做任何其他表格的查詢數(shù)據(jù)。
在傳遞表值變量到程序中時必須使用 READONLY從句。表格變量里的數(shù)據(jù)不能做修改——除了修改你可以把數(shù)據(jù)用于任何其他的操作。另外，你也不能把表格變量用做OUTPUT參數(shù)——只能用做input參數(shù)。
使用自己的新表格變量類型
首先，要聲明一個變量類型SalesHistoryTableType，不需要再一次定義表格結(jié)構(gòu)，因為在創(chuàng)建這個表格類型的時候已經(jīng)定義過了。
DECLARE @DataTable AS SalesHistoryTableType
The following script adds 1,000 records into my @DataTable table variable:
DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=1000)
BEGIN
INSERT INTO @DataTable(Product, SaleDate, SalePrice)
VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))
INSERT INTO @DataTable(Product, SaleDate, SalePrice)
VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))
INSERT INTO @DataTable(Product, SaleDate, SalePrice)
VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))
SET @i = @i + 1
END
只要把數(shù)據(jù)加載到表格變量里，就可以把結(jié)構(gòu)傳遞到存儲過程中。
注意：當(dāng)表格變量作為參數(shù)傳遞后，表格會在存儲在tempdb系統(tǒng)數(shù)據(jù)庫里，而不是傳遞整個數(shù)據(jù)集在內(nèi)存里。因為這樣保證高效處理大批量數(shù)據(jù)。所有服務(wù)器方的表格變量參數(shù)傳遞都是通過使用reference調(diào)用tempdb中的表格。
EXECUTE usp_InsertBigScreenProducts
@TableVariable = @DataTable
想要查詢程序是否和預(yù)想效果一樣，可以執(zhí)行以下查詢來看記錄是否已經(jīng)插入到 SalesHistory表格中：
SELECT * FROM SalesHistory
總結(jié)：
雖然SQL Server 2008的參數(shù)傳遞功能的使用還有一些小小的局限性，比如不能修改參數(shù)中的數(shù)據(jù)和把變量用于output，但是它大大提高了程序性能，它可以減少server往返旅程數(shù)、利用表格限制并擴(kuò)展編程在數(shù)據(jù)庫引擎中的功能。