如何在存储过程B中，对存储过程A的结果集进行查询

 

问题：

 

有读者来信询问：如何在存储过程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完全实战」一书。