章立民研究室

問題：

 

有讀者來信詢問：如何在預存程序 B 裡面使用預存程序 A 執行後的結果集？就是在預存程序 B 裡面再對預存程序A的結果集進行查詢。 

解答： 

建議您在預存程序中將另外一個預存程序的結果集先存放到一個暫存資料表，接著對此暫存資料表進行查詢處理之後，然後再將最終的處理結果傳出。 

在此我們將示範如何在預存程序中使用名稱以 # 開頭的區域性暫存資料表以及 table 資料型別的變數來儲存另外一個預存程序的結果集，並進行後續的處理。 

程式範例一 

圖表 1 

以下的程式碼會先建立一個查詢所有薪資與性別的預存程序（uspGetIncome），接著建立一個以「性別」為查詢條件的預存程序（uspGetTotalIncomeByGender）。在預存程序 uspGetTotalIncomeByGender 中，我們會建立一個名稱為 #tmpTable 的暫存資料表，然後使用 INSERT INTO ... EXECUTE <預存程序>  陳述式將另外一個預存程序 uspGetIncome 的結果集新增至暫存資料表中，接著以「性別」為分類條件，使用  SUM  函式來計算出不同性別的收入總計。最後，我們分別以男性與女性為輸入參數呼叫預存程序 uspGetTotalIncomeByGender（執行結果如圖表1所示）： 

...

-- 查詢目前薪資之SP
CREATE PROCEDURE dbo.uspGetIncome
AS
SELECT 目前薪資, 性別 FROM 章立民研究室;
GO

-- 建立以「性別」為查詢條件的 SP
-- 這個 SP 會呼叫上面的 SP
CREATE PROCEDURE dbo.uspGetTotalIncomeByGender
@Gender nvarchar(1)
AS
-- 建立一個暫存資料表
CREATE TABLE #tmpTable (目前薪資 money, 性別 nvarchar(1));
INSERT INTO #tmpTable EXECUTE dbo.uspGetIncome;
-- 取得以性別為分類條件的收入總計
SELECT SUM(目前薪資) AS 收入總計 FROM #tmpTable WHERE 性別 = @Gender;
GO

-- 查詢性別為「男」的員工收入總計
EXECUTE dbo.uspGetTotalIncomeByGender '男';
GO

-- 查詢性別為「女」的員工收入總計
-- 省略  EXECUTE 關鍵字
-- 並指定 @Gender 這個參數名稱來執行SP
-- 請注意，此時 SP 必須是陳述式的第一道指令
dbo.uspGetTotalIncomeByGender @Gender = '女';
GO

程式範例二 

 

圖表 2 

本程式範例示範如何使用 table 資料型別的變數來暫存預存程序 uspGetAllEmployees 的結果集，它會從「章立民研究室」資料表查詢出所有員工的姓名、性別以及部門等資料。另外一個預存程序 uspGetEmployeeByDeptAndGender 需要兩個輸入參數，分別用來篩選部門與性別之用。 

由於我們已經將預存程序 uspGetAllEmployees 的結果集儲存到 table 資料型別的變數中，此時只需在 SELECT 陳述式的 WHERE 條件子句中，將輸入參數帶入，即可由 table 資料型別變數中，將某個部門之特定性別的員工資料查詢出來（執行結果如圖表 2 所示）。程式碼如下所列： 

...
-- 取得所有員工資料之 SP
CREATE PROCEDURE dbo.uspGetAllEmployees
AS
SELECT 姓名, 性別, 部門 FROM 章立民研究室
GO

-- 建立以「部門」、「性別」為查詢條件的 SP
-- 這個 SP 會呼叫上面的 SP
CREATE PROCEDURE dbo.uspGetEmployeeByDeptAndGender
@Dept nvarchar(10),
@Gender nvarchar(1)
AS
-- 建立一個 table 資料型別的變數
DECLARE @tmpTable table (姓名 nvarchar(10), 性別 nvarchar(1), 部門 nvarchar(10));
INSERT INTO @tmpTable EXECUTE dbo.uspGetAllEmployees;
SELECT * FROM @tmpTable WHERE 部門 = @Dept AND 性別 = @Gender ORDER BY 1;
GO

-- 查詢部門為「業務部」、性別為「女」性的員工資料
-- 指明 SP 的參數名稱，因此參數順序可以不按照 SP 的參數順序
EXECUTE dbo.uspGetEmployeeByDeptAndGender @Gender = '女', @Dept = '業務部';
GO

-- 與上個範例相同，但是沒有指明 SP 的參數名稱
-- 因此參數順序必須依照 SP 的參數順序
EXECUTE dbo.uspGetEmployeeByDeptAndGender '業務部', '女';
GO

-- 與上個範例相同，只是省略 EXECUTE 這個關鍵字來執行 SP
dbo.uspGetEmployeeByDeptAndGender '業務部', '女';
GO 

附註：
關於使用table資料型別的好處，請參考章立民研究室所撰寫的「Microsoft® SQL Server 2005完全實戰」一書。