SQLserver2008如何把表格變數傳遞到預存程序中

標籤：

在Microsoft SQL Server 2008中，你可以實現把表格變數傳遞到預存程序中，如果變數可以被聲明，那麼它就可以被傳遞。下面我們來具體介紹如何把表格變數(包括內含的資料)傳遞到預存程序和功能中去。 

 

傳遞表值參數 使用者經常會碰到許多需要把數值容器而非單個數值放到預存程序裡的情況。對於大部分的程式設計語言而言，把容器資料結構傳遞到常式裡或傳遞出來是很常見而且很必要的功能。TSQL也不例外。SQL Server 2000通過OPENXML可以實現這個功能，使用者可以把資料存放區為VARCHAR資料類型然後進行傳遞。到了SQL Server 2005，隨著 XML資料類型以及XQuery的出現，這個功能變得容易一點。但使用者仍然需要對XML資料進行組建和粉碎才能夠使用它，因此這個功能使用起來並不簡單。SQL Server 2008則能夠把表值資料類型傳遞到預存程序和功能中，從而大大地簡化了編程的工作，因為程式員無需再花心思去組建和解析XML資料了。該功能還可以讓客戶方開發員傳遞客戶方資料表格到資料庫中。 

怎樣傳遞表格參數? 以銷售為例，首先建立一個 my 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

建立表值參數第一步是建立確切的表格類型，這一步非常重要，因為這樣你就可以在資料庫引擎裡定義表格的結構，讓你可以在需要的時候在過程代碼裡使用該表格。下面的代碼建立 SalesHistoryTableType 表格類型定義： 

 

CREATE TYPE SalesHistoryTableType AS TABLE (

[Product] [varchar](10) NULL， 

[SaleDate] [datetime] NULL， 

[SalePrice] [money] NULL ) 

GO

如果想要查看系統裡其他類型的表格類型定義，你可以執行下面這個查詢命令，查看系統目錄： SELECT * FROM sys.table_types我們需要定義用來處理表值參數的預存程序。下面這個程式能夠接受指定SalesHistoryTableType類型的表值參數，並載入到SalesHistory中，表值參數在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

 

傳遞的表格變數還可以用做任何其他表格的查詢資料。 傳遞表值參數功能的局限性 在傳遞表值變數到程式中時必須使用 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系統資料庫裡，而不是傳遞整個資料集在記憶體裡。因為這樣保證高效處理大批量資料。所有伺服器方的表格變數參數傳遞都是通過使用reference調用tempdb中的表格。 EXECUTE usp_InsertBigScreenProducts @TableVariable = @DataTable想要查詢程式是否和預想效果一樣，可以執行以下查詢來看記錄是否已經插入到 SalesHistory表格中： SELECT * FROM SalesHistory結論： 雖然SQL Server 2008資料庫的參數傳遞功能的使用還有一些局限性，比如不能修改參數中的資料和把變數用於output，但它已經很大程度的提高了程式效能，它可以減少server往返旅程數、利用表格限制並擴充編程在資料庫引擎中的功能。

SQLserver2008如何把表格變數傳遞到預存程序中