MS SQL Server的存储过程与ADO记录集关系的剖析（朱亦文又一杰作）

wuaza

MS SQL Server的存储过程与ADO记录集关系的剖析

作者：朱亦文
日期：2006.06.23

一、运行示例

1、原始示例

首先，在 Access 2003 中通过菜单选择[帮助] - [示例数据库] - [罗斯文示例 Access 项目]打开Access自带的示例项目NorthwindCS.ADP，在数据库窗口中选择[查询] - [新建] - [新建文本存储过程]，来建立下面的下面的这个存储过程：

1CREATE PROCEDURE spSample
2AS
3BEGIN
4    -- 生成临时表 #tTemp1
5    SELECT 雇员ID, 姓氏, 名字, 职务, 尊称, 地址 INTO #tTemp1 FROM 雇员
6
7    -- 创建临时表 #tTemp2
8    CREATE TABLE #tTemp2 (雇员ID INT, 尊称 VARCHAR(), 地址 VARCHAR(60))
9
10    PRINT '输出数据'
11
12    -- 输出临时表 #tTemp1 数据
13    SELECT * FROM #tTemp1
14
15    -- 将临时表 #tTemp1 的数据插入到 #tTemp2
16    INSERT INTO #tTemp2 SELECT 雇员ID, 尊称, 地址 FROM #tTemp1
17
18    -- 从临时表 #tTemp2 输出“尊称”为“先生”的记录
19    SELECT * FROM #tTemp2 WHERE 尊称 = '先生'
20END然后，在模块中新建下面的VBA的测试函数。

<DIV > 1Public Function TestSP(ByVal sp As String) As Boolean
2    On Error GoTo ErrCode
3    Dim oRs As ADODB.Recordset
4    Dim i As Integer
5   
6    Set oRs = CurrentProject.Connection.Execute(sp)
7   
8    If Not (oRs Is Nothing) Then
9        Do While Not (oRs Is Nothing)
10            i = i + 1
11            Debug.Print "*** 第 " & i & " 个记录集 *** " & _

wmok

楼主能否再做个在同一个存储过程中多表连接并且相互调用参数的例子啊？！

例如：样品转订单，订单转采购，已采购转入库 等等。。。

sxm1967

做个实例更好

china007

感谢提供，谢谢[em04]

wuaza

二、分析

在原始示例中，总共产生了 5 个记录集，其中有 3 个是附加数据集，即包含有关受 Transact-SQL 语句影响的行数的信息，分别是如下三条语句：

    SELECT 雇员ID, 姓氏, 名字, 职务, 尊称, 地址 INTO #tTemp1 FROM 雇员
    PRINT '输出数据'
    INSERT INTO #tTemp2 SELECT 雇员ID, 尊称, 地址 FROM #tTemp1

对应于：

*** 第 1 个记录集 *** 已关闭!
-----------==== 未返回记录! ====-----------

*** 第 2 个记录集 *** 已关闭!
-----------==== 未返回记录! ====-----------

*** 第 4 个记录集 *** 已关闭!
-----------==== 未返回记录! ====-----------

其中 SELECT INTO 和 INSERT INTO 语句会产生“影响的行数的信息”的附加集，且记录集是关闭的，PRINT 语句是用来作为调试信息的，同样会产生“影响的行数的信息”的附加集，且记录集是关闭的。

在 Access 或 ADO 记录中打开存储过程时会将第一个记录集作为结果集，由于第一个记录集是关闭的，因此会产生“存储过程执行成功但未返回记录。”的提示，如果把这个存储过程作为窗体或报表的数据源，也同会产生“存储过程执行成功但未返回记录。”的提示，而导致无法正常打窗体或报表。因此，这也就是大多数开发人员遇到应用存储过程不成功的地方。

在改进示例中，在存储过程中的开始加入了 SET NOCOUNT ON 设置，它的作用就是来防止干扰 SELECT 语句的额外的结果集。SQL Server 2000联机帮助是这样解释的：使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。当 SET NOCOUNT 为 OFF 时，返回计数。

这样一来，就只返回了两个 SELECT 语句的记录集（SELECT INTO语句除外，它只产生额外的数据集）。其它的 SQL 语句只产生额外的数据集，被 SET NOCOUNT ON 设置所屏蔽。

在 Access 或 ADO 记录中打开存储过程时会将第一个 SELECT 语句产生的记录集作为结果集。

事实上，在实际应用中，我们通常只要返回一个 SELECT 语句产生的记录，其它多余的 SELECT 语句产生的结果集一般都只作为调试使用。因此，我们在调试完存储过程后，一定要把多余的 SELECT 注释或删除，以免影响正常的输出。

wuaza

2、改进示例


接下来，修改存储过程spSample，在存储过程的 BEGIN 语句之后插入 SET NOCOUNT ON 语句来防止干扰 SELECT 语句的额外的结果集：

1ALTER PROCEDURE spSample
2AS
3BEGIN
4    -- SET NOCOUNT ON added to prevent extra result sets from
5    -- interfering with SELECT statements.
6    -- 加入 SET NOCOUNT ON 语句来防止干扰 SELECT 语句的额外的结果集
7    SET NOCOUNT ON
8   
9    -- 生成临时表 #tTemp1
10    SELECT 雇员ID, 姓氏, 名字, 职务, 尊称, 地址 INTO #tTemp1 FROM 雇员
11
12    -- 创建临时表 #tTemp2
13    CREATE TABLE #tTemp2 (雇员ID INT, 尊称 VARCHAR(25), 地址 VARCHAR(60))
14
15    PRINT '输出数据'
16
17    -- 输出临时表 #tTemp1 数据
18    SELECT * FROM #tTemp1
19
20    -- 将临时表 #tTemp1 的数据插入到 #tTemp2
21    INSERT INTO #tTemp2 SELECT 雇员ID, 尊称, 地址 FROM #tTemp1
22
23    -- 从临时表 #tTemp2 输出“尊称”为“先生”的记录
24    SELECT * FROM #tTemp2 WHERE 尊称 = '先生'
25END

再在 VBE 的立即窗口，输入：

? TestSP("spSample")

在立即窗口中看到如下结果：

<DIV PADDING-RIGHT: 5px; BORDER-TOP: PADDING-LEFT: FONT-SIZE: 13px; PADDING-BOTTOM: BORDER-LEFT: WIDTH: 98%; WORD-BREAK: break-all; PADDING-TOP: 4px; BORDER-BOTTOM: #cccccc 1px solid; BACKGROUND-COLOR: #eeeeee?>*** 第 1 个记录集 *** 已打开
<IMG src="http://zhuyiwen.cnblo