Sql Server 2008中存儲過程傳入表值參數(shù)

SQL Server 2008中SQL應(yīng)用系列--目錄索引

經(jīng)常我們會遇到需要一次往數(shù)據(jù)表中插入多行數(shù)據(jù)。此時，最簡單的莫過于調(diào)用存儲過程。

比如目標(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)用存儲過程插入一個行集

/*************一次插入一個行集******************/
/*********?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)用存儲過程即可。這樣做的壞處是顯而易見的。于是有了改進(jìn)方案：

/*************一次插入五個行集******************/
/*********?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值和允許插入等異常。

幸運的是，SQL Server　2008提供了新的表值參數(shù)，可以一次性傳入并處理多個行集。

示例如下:

/*************一次插入N個行集******************/
/*********?3w@live.cn?邀月***************/

----首先，我們定義一個表值參數(shù)類型，其實就是一個表變量
Create?type?dbo.tp_Demo_MultiRowsInsert?as?Table
(
[PName]?[Nvarchar](20)?NOT?NULL,
[GName]?[Nvarchar](20)?NOT?NULL
)
GO

----下面我們用這個表變量做參數(shù)，通過存儲過程調(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)造多個行集
DECLARE?@tmpRows?as?tp_Demo_MultiRowsInsert

----插入多個數(shù)據(jù)到參數(shù)表中
INSERT?@tmpRows(PName,GName)?values('胡一刀','國土資源部')
INSERT?@tmpRows(PName,GName)?values('胡青牛','醫(yī)藥局')
INSERT?@tmpRows(PName,GName)?values('令狐沖','文廣中心')

----傳遞參數(shù)到存儲過程，完成一次多行集插入
EXEC?dbo.CPP_InsertMultiRows?@tmpRows

?注意：這種一次轉(zhuǎn)入多個參數(shù)的模式與SQL Servrer 2008新增的Values（(group1),(group2),...,(groupn)）的方式很類似，后者的應(yīng)用可以看這里：

http://www.cnblogs.com/downmoon/archive/2010/10/12/1849176.html

小結(jié)：SQL Server 2008 新增參數(shù)類型－表值參數(shù)。使用用戶定義的表類型來聲明，借助它，可以不必創(chuàng)建臨時表或許多參數(shù)，即可向存儲過程發(fā)送多行數(shù)據(jù)。對于某些繁忙的系統(tǒng)，這減少了應(yīng)用程序和數(shù)據(jù)庫服務(wù)器之間的交互，從而減少了占用的帶寬，數(shù)據(jù)庫端的事務(wù)處理更高效。