如何在sqlserver2008 中傳遞表值參數

前言:

sqlserver 2008 將在2008年二月正式發布,為了方便大家提前了解2008的新功能,我翻譯了一些國外網站的的技術資料,希望可以幫助大家. 本人翻譯水平有限,希望大家看後可以指出錯誤.

 

我們總是希望可以傳遞表變量給存儲過程，如果變量可以聲明，那麼表變量就可以被傳遞了。這裡呢我介紹如何傳遞表變量（以及變量中的數據）給存儲過程和函數。
 
為什麼要將表做為參數傳遞？
這麼多年來，我們會經常遇到需要將一組值而不是單個值傳遞給存儲過程。在多數其他程序語言中，這樣的能力很普通。但TSQL不同，因為數據庫是存放數據的地方。
在sqlserver2000中可以使用OPENXML存放數據到一個VARCHAR數據類型中並傳遞它。在sqlserver2005中簡單的利用XML數據類型和XQuery可以實現。但即使這樣，在你使用數據前你仍然需要先構造和分裂這些XML數據；在SQLserver2008你可以直接傳遞一個table數據類型給存儲過程和函數。因為不需要考慮構造和分析XML數據，所以它極大地簡化了開發的過程。這個功能也允許客戶端開發人員像普通參數一樣傳遞數據表給服務器端。
 
如何傳遞表參數：
先建立SalesHistory表，存放我的產品銷售數據。下面的代碼在我選擇的數據庫中建立表：
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
 
第一步建立要使用的表值參數的table類型；這一步是必須的，只有這樣，表結構才可以定義在數據庫中，然後再在你的代碼中使用它。下面的代碼建立一個SalesHistoryTableType表類型定義：
CREATE TYPE SalesHistoryTableType AS TABLE
(
[Product] [varchar](10) NULL,

[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO
如果你想查看你系統中定義的table類型，可以執行下面的查詢查看系統目錄：
SELECT * FROM sys.table_types
 
我定義一個用來處理表值參數的存儲過程。下面的過程處理一個為SalesHistoryTableType類型的表值參數，並將表值中Product列值為“BigScreen”的記錄導入到SalesHistory中。
CREATE PROCEDURE usp_InsertBigScreenProducts
(
@TableVariable SalesHistoryTableType READONLY
)
AS
BEGIN
INSERT INTO SalesHistory
(
Product, SaleDate, SalePrice
)
SELECT
Product, SaleDate, SalePrice
FROM
@TableVariable
WHERE

an>Product = ''BigScreen''

END
GO
你可以像其他表一樣查詢表變量中的數據。
 
傳遞表變量的限制：
當你傳遞表值變量給過程的時候，你必須指定READONLY。表變量中的數據不可以修改，也不能用作OUTPUT參數，只可以用作INPUT參數。
 
使用表變量：
首先，我需要聲明一個類型為SalesHistoryTableType的變量。我不需要在此定義表結構，因為已經在定義類型的時候定義了。
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
一旦我將數據導入到表變量中，我就可以傳遞這個結構到存儲過程中（確定你添加數據到表變量並傳遞這個變量到過程，當存儲過程返回時這個變量就停止活動）。
 
注意：當表變量作為參數傳遞的時候，這個表的數據集存在於tempdb系統數據庫而不是內存中。這樣呢在處理大量數據的時候相當有效。所有服務器端傳遞的表變量參數都是通過引用傳遞的，一個引用就是指向tempdb中表的指針。
EXECUTE usp_InsertBigScreenProducts
@TableVariable = @DataTable
 
 
需要考慮的：
SQL Server 2008表參數的特點是開發和性能上一個很大的進步。利用這種功能你可以：減少服務往返，使用它擴充程序功能。

有些限制你需要記住，像不可以修改參數中的數據、不能使用變量作為OUTPUT。盡管有這樣一些缺點，但當SQL Server 2008發布的時候，我仍然會繼續探索表參數的特點。