SQL Server 2008表值參數的創建和使用步驟

表值參數(Table-valued parameter)是SQL Server數據庫2008的新特性之一,在以往的版本中,我們沒有辦法把表變量當作一個參數傳遞給存儲過程。但在微軟的SQL Server 2008中引入了表值參數這個特性,它可以實現此類功能。

表值參數有兩個明顯的優點:

1:不需要爲初始的數據加鎖。

2:它不會導致語句重新編譯。

表值參數的創建和使用包括以下步驟:

(1) 創建表類型

(2) 創建一個可將表類型作爲參數來接受的存儲過程或函數

(3) 創建表變量並插入數據

(4) 調用該存儲過程和函數,並將表變量作爲參數傳遞。

下面,我們來一步步分解這個創建和使用的過程。首先,我們用以下的DDL SQL語句來創建一個名爲“TestDB”的測試數據庫:

USE [master]

GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')

DROP DATABASE TestDB

GO

Create database TestDB

go

下面,使用以下的DDL SQL語句來創建一個名爲TestLocationTable的表:

USE [TestDB]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].

[TestLocationTable]') AND type in (N'U'))

DROP TABLE [dbo].[TestLocationTable]

GO

USE [TestDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[TestLocationTable](

[Id] [int] NULL,

[shortname] [char](3) NULL,

[name] [varchar](100) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

然後,使用以下的DML SQL語句將數據添加到我們上面創建的表中:

USE [TestDB]

GO

insert into TestLocationTable ( Id, shortname, Name) select 1, 'NA1', 'NewYork'

insert into TestLocationTable ( Id, shortname, Name) select 2, 'NA2', 'NewYork'

insert into TestLocationTable ( Id, shortname, Name) select 3, 'NA3', 'NewYork'

insert into TestLocationTable ( Id, shortname, Name) select 4, 'EU1', 'London'

insert into TestLocationTable ( Id, shortname, Name) select 5, 'EU2', 'London'

insert into TestLocationTable ( Id, shortname, Name) select 6, 'AS1', 'Tokyo'

insert into TestLocationTable ( Id, shortname, Name) select 7, 'AS2', 'HongKong'

go

下面,我們需要創建一個和TestLocationTable表具有相似表結構的表類型(TABLE TYPE),語句如下所示:

USE [TestDB]

GO

IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id

WHERE st.name = N'OfficeLocation_Tabetype' AND ss.name = N'dbo')

DROP TYPE [dbo].[OfficeLocation_Tabetype]

GO

USE [TestDB]

GO

CREATE TYPE [dbo].[OfficeLocation_Tabetype] AS TABLE(

[Id] [int] NULL,

[shortname] [char](3) NULL,

[name] [varchar](100) NULL

)

GO

接下來,需要創建一個可以將表類型作爲一個參數來接受的存儲過程,使用的語句如下:

USE [TestDB]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].

[usp_InsertProdLocation]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[usp_selectProdLocation]

GO

CREATE PROCEDURE usp_InsertProdLocation

@TVP OfficeLocation_Tabetype READONLY

AS

SET NOCOUNT ON

INSERT INTO TestLocationTable Select ID, shortname, name from @TVP

where convert(varchar(10),id)+shortname+name not in (select

convert(varchar(10),id)+shortname+name from TestLocationTable)

GO

此存儲過程將表變量作爲導入值接收,並且只插入TestLocationTable中沒有的數據。現在,大家可以嘗試創建一個表變量,並執行上面創建的存儲過程usp_InsertProdLocation,語句如下所示:

use TestDB

go

DECLARE @TV AS [OfficeLocation_Tabetype]

INSERT INTO @TV (Id, Shortname, Name) SELECT 12, 'ME1', 'Dubai'

INSERT INTO @TV (Id, Shortname, Name) SELECT 13, 'ME2', 'Tehran'

INSERT INTO @TV (Id, Shortname, Name) SELECT 17, 'EA1', 'Bombay'

INSERT INTO @TV (Id, Shortname, Name) SELECT 18, 'EA2', 'Karachi'

INSERT INTO @TV (Id, Shortname, Name) SELECT 3, 'NA3', 'NewYork'

INSERT INTO @TV (Id, Shortname, Name) SELECT 4, 'EU1', 'London'

exec usp_InsertProdLocation @TV

go

此時,可以使用以下的TSQL語句從表TestLocationTable查詢所有的數據:

use TestDB

go

select * from TestLocationTable

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

(11 row(s) affected)

從返回的結果看,存儲過程usp_InsertProdLocation 插入了表變量@TV中和表TestLocationTable所有不匹配的行。

另外,我們還可以將表變量傳遞給一個函數。下面創建一個簡單的函數,語句如下所示:

USE [TestDB]

GO

IF EXISTS (SELECT * FROM sys.objects

WHERE object_id = OBJECT_ID(N'[dbo].[myfunction]')

AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[myfunction]

GO

create function dbo.myfunction (@TV OfficeLocation_Tabetype READONLY)

returns int

as

begin

declare @i int

set @i=(Select COUNT(*) from @TV)

return @i

end

現在,大家可以通過創建一個表變量並將該變量作爲一個參數傳遞給已創建的函數以調用該函數,該語句如下所示:

USE [TestDB]

GO

DECLARE @TV AS [OfficeLocation_Tabetype]

INSERT INTO @TV (Id, Shortname, Name) SELECT 12,'ME1','Dubai'

INSERT INTO @TV (Id, Shortname, Name) SELECT 13,'ME2','Tehran'

INSERT INTO @TV (Id, Shortname, Name) SELECT 17,'EA1','Bombay'

INSERT INTO @TV (Id, Shortname, Name) SELECT 18,'EA2','Karachi'

INSERT INTO @TV (Id, Shortname, Name) SELECT 3,'NA3','NewYork'

INSERT INTO @TV (Id, Shortname, Name) SELECT 4,'EU1','London'

select dbo.myfunction(@TV)

go

執行的結果:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

-----------

6

注釋:上文中的參考腳本已在SQL Server 2008 CTP6版本上進行編寫並已經測試成功。

SQL Server 2008新功能─傳遞表值參數
許多人一直希望能夠實現把表格變量傳遞到存儲過程中,如果變量可以被聲明,那麽它就應該能夠被傳遞。而最新的SQL Server 2008則有這項功能!想知道如何才能把表格變量(包括內含的數據)傳遞到存儲過程和功能中去嗎? 爲...查看完整版>>SQL Server 2008新功能─傳遞表值參數
 
使用C#創建SQL Server的存儲過程
介紹通常,開發人員使用的是T-SQL來創建SQL Server的存儲過程、函數和觸發器。 而現在的SQL Server 2005已經完全支持.NET通用語言運行時(CLR)了。 這就意味著,你可以使用.NET的語言,如C#、VB.NET之類的來開發SQL...查看完整版>>使用C#創建SQL Server的存儲過程
 
使用 SQL SERVER 2005 新增功能--- DDL_DATABASE_LEVEL_EVENTS 數據庫級的觸發器,創建數據庫日志表
爲數據庫設計人員提供了便捷,創建一個日志表,當用戶添加,刪除數據庫對象的時候,記錄下來詳細的情況,也可以爲數據庫管理員的還原提供了參考,因爲裏面記錄了准確的操作時間,值得大家參考。USE GOSET ANSI_NULLS...查看完整版>>使用 SQL SERVER 2005 新增功能--- DDL_DATABASE_LEVEL_EVENTS 數據庫級的觸發器,創建數據庫日志表
 
使用SQL Server 2008管理非結構化數據
使用SQL Server 2008管理非結構化數據
這篇論壇文章(賽迪網技術社區)詳細的講解了使用SQL Server 2008管理非結構化數據的具體方法,更多內容請參考下文: microsoft SQL Server™ 2008提供了一個靈活的解決方案,使得可以存儲非結構化數據,並將它...查看完整版>>使用SQL Server 2008管理非結構化數據
 
講解使用SQL Server升級顧問的詳細步驟
微軟提供了SQL Server 2008升級顧問(SQL Server 2008 Upgrade Advisor,SSUA),可以在更新數據庫及相關程序前幫助用戶檢測並識別可能遇到的升級問題。 該工具可以檢查SQL Server 2000和 SQL Server 2005數據庫,找出...查看完整版>>講解使用SQL Server升級顧問的詳細步驟
 
SQL Server 2005 創建登錄時提示“不支持 MUST_CHANGE”
錯誤:此版本的 Microsoft Windows 不支持 MUST_CHANGE 選項。 (Microsoft SQL Server,錯誤: 15195)解決:只需創建登錄時取消強制密碼過期選項。...查看完整版>>SQL Server 2005 創建登錄時提示“不支持 MUST_CHANGE”
 
SQL Server聯機叢書:存儲過程及其創建
存儲過程可以使得對數據庫的管理、以及顯示關于數據庫及其用戶信息的工作容易得多。存儲過程是 SQL 語句和可選控制流語句的預編譯集合,以一個名稱存儲並作爲一個單元處理。存儲過程存儲在數據庫內,可由應用程序通過...查看完整版>>SQL Server聯機叢書:存儲過程及其創建
 
寫注冊表動態創建SQL Server ODBC數據源
各個參數的意義:DBServer: 數據庫所在主機DBName: 數據庫名稱DSN: 數據源名稱UID: 登陸用戶返回值:-1: 沒有安裝SQL Server驅動程序-2: 其他錯誤0: 成功實現函數:int MakeSQLServerODBCDSN(LPCTSTR DBServer,...查看完整版>>寫注冊表動態創建SQL Server ODBC數據源
 
動態創建SQL Server數據庫、表、存儲過程等架構信息
動態創建SQL Server數據庫、表、存儲過程等架構信息 作者: 孟憲會 出自: 【孟憲會之精彩世界】 發布日期: 2003-8-17 23:34:35 下面是利用SQL語句創建數據庫、表、存儲過程、視圖、索引、規則、修改表、查看數據等...查看完整版>>動態創建SQL Server數據庫、表、存儲過程等架構信息
 
 
回到王朝網路移動版首頁