Sql Server 2008中存儲(chǔ)過(guò)程傳入表值參數(shù)
SQL
Server 2008中SQL應(yīng)用系列--目錄索引
經(jīng)常我們會(huì)遇到需要一次往數(shù)據(jù)表中插入多行數(shù)據(jù)。此時(shí)，最簡(jiǎn)單的莫過(guò)于調(diào)用存儲(chǔ)過(guò)程。
比如目標(biāo)表如下：
USE?testDb2
GO
IF?NOT?OBJECT_ID('tb_Demo_MultiRowsInsert')?IS?NULL
DROP?TABLE?[tb_Demo_MultiRowsInsert]
/******?Object:?Table?[dbo].[tb_Demo_MultiRowsInsert]?Script?Date:?2012/4/6?12:19:21?******/
CREATE?TABLE?[dbo].[tb_Demo_MultiRowsInsert](
[TeamID]?int?not?null?primary?key?identity(101,1),
[PName]?[Nvarchar](20)?NOT?NULL,
[GName]?[Nvarchar](20)?NOT?NULL
)
GO調(diào)用存儲(chǔ)過(guò)程插入一個(gè)行集
/*************一次插入一個(gè)行集******************/ /*********?3w@live.cn?邀月***************/ Create?Procedure?CPP_InsertOneRows (@PName?Nvarchar(20)='', @GName?Nvarchar(20)='' ) as INSERT?[dbo].[tb_Demo_MultiRowsInsert]?(PName,GName) VALUES(@PName,@GName) GO
如果需要多行，則循環(huán)調(diào)用存儲(chǔ)過(guò)程即可。這樣做的壞處是顯而易見(jiàn)的。于是有了改進(jìn)方案：
/*************一次插入五個(gè)行集******************/ /*********?3w@live.cn?邀月***************/ Create?Procedure?CPP_InsertFiveRows ( @PName1?Nvarchar(20)='', @GName1?Nvarchar(20)='', @PName2?Nvarchar(20)='', @GName2?Nvarchar(20)='', @PName3?Nvarchar(20)='', @GName3?Nvarchar(20)='', @PName4?Nvarchar(20)='', @GName4?Nvarchar(20)='', @PName5?Nvarchar(20)='', @GName5?Nvarchar(20)='' ) as INSERT?[dbo].[tb_Demo_MultiRowsInsert]?(PName,GName) VALUES(@PName1,@GName1) INSERT?[dbo].[tb_Demo_MultiRowsInsert]?(PName,GName) VALUES(@PName2,@GName2) INSERT?[dbo].[tb_Demo_MultiRowsInsert]?(PName,GName) VALUES(@PName3,@GName3) INSERT?[dbo].[tb_Demo_MultiRowsInsert]?(PName,GName) VALUES(@PName4,@GName4) INSERT?[dbo].[tb_Demo_MultiRowsInsert]?(PName,GName) VALUES(@PName5,@GName5) GO
這樣，一次可以插入五條數(shù)據(jù)，當(dāng)然十條也可以。但明顯也有不足，首先，如果剛好要插入3條數(shù)據(jù)，那么應(yīng)該避免null值和允許插入等異常。
幸運(yùn)的是，SQL Server 2008提供了新的表值參數(shù)，可以一次性傳入并處理多個(gè)行集。
示例如下:
/*************一次插入N個(gè)行集******************/
/*********?3w@live.cn?邀月***************/
----首先，我們定義一個(gè)表值參數(shù)類(lèi)型，其實(shí)就是一個(gè)表變量
Create?type?dbo.tp_Demo_MultiRowsInsert?as?Table
(
[PName]?[Nvarchar](20)?NOT?NULL,
[GName]?[Nvarchar](20)?NOT?NULL
)
GO
----下面我們用這個(gè)表變量做參數(shù)，通過(guò)存儲(chǔ)過(guò)程調(diào)用它
CREATE?Procedure?dbo.CPP_InsertMultiRows
(@ManyRows?as?tp_Demo_MultiRowsInsert?readonly
)
as
INSERT?[dbo].[tb_Demo_MultiRowsInsert]
SELECT?PName,GName?from?@ManyRows
GO
----程序中構(gòu)造多個(gè)行集
DECLARE?@tmpRows?as?tp_Demo_MultiRowsInsert
----插入多個(gè)數(shù)據(jù)到參數(shù)表中
INSERT?@tmpRows(PName,GName)?values('胡一刀','國(guó)土資源部')
INSERT?@tmpRows(PName,GName)?values('胡青牛','醫(yī)藥局')
INSERT?@tmpRows(PName,GName)?values('令狐沖','文廣中心')
----傳遞參數(shù)到存儲(chǔ)過(guò)程，完成一次多行集插入
EXEC?dbo.CPP_InsertMultiRows?@tmpRows?注意：這種一次轉(zhuǎn)入多個(gè)參數(shù)的模式與SQL Servrer 2008新增的Values（(group1),(group2),...,(groupn)）的方式很類(lèi)似，后者的應(yīng)用可以看這里：
http://www.cnblogs.com/downmoon/archive/2010/10/12/1849176.html
小結(jié)：SQL Server 2008 新增參數(shù)類(lèi)型－表值參數(shù)。使用用戶(hù)定義的表類(lèi)型來(lái)聲明，借助它，可以不必創(chuàng)建臨時(shí)表或許多參數(shù)，即可向存儲(chǔ)過(guò)程發(fā)送多行數(shù)據(jù)。對(duì)于某些繁忙的系統(tǒng)，這減少了應(yīng)用程序和數(shù)據(jù)庫(kù)服務(wù)器之間的交互，從而減少了占用的帶寬，數(shù)據(jù)庫(kù)端的事務(wù)處理更高效。
下載文檔
