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

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的测试函数。

 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 & " 个记录集 *** " & _
12                IIf(oRs.State = 0, "已关闭!", IIf(oRs.State Mod 2 > 0, "已打开", "其它状态"))
13            If oRs.State Mod 2 > 0 Then
14                Call ListRecord(oRs)
15            Else
16                Debug.Print "-----------==== 未返回记录! ====-----------": Debug.Print
17            End If
18            
19            Set oRs = oRs.NextRecordset
20        Loop
21        TestSP = True
22    Else
23        TestSP = False
24    End If
25    Debug.Print "*** 总共有 " & i & " 个记录集 ***"
26    Exit Function
27
28ErrCode:
29    Debug.Print "==========================================="
30    Debug.Print "错误：" & Err.Number, "描述：" & Err.Description
31    Debug.Print "==========================================="
32    Debug.Print
33    Err.Clear
34    Resume Next
35End Function
36
37Public Sub ListRecord(ByRef rs As ADODB.Recordset)
38    Dim i As Integer, j As Integer
39    Dim aa As String
40    
41    j = rs.Fields.Count
42    Debug.Print "==========================================="
43    
44    For i = 0 To j - 1
45        Debug.Print rs.Fields(i).Name & Chr(9);
46    Next
47    Debug.Print
48    Debug.Print "==========================================="
49    
50    If Not rs.EOF Then
51        aa = rs.GetString(, , Chr(9), Chr(13))
52        Debug.Print aa
53    End If
54    
55    Debug.Print
56End Sub

打开 VBE 的立即窗口，输入：

? TestSP("spSample")

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

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

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

*** 第 3 个记录集 *** 已打开
===========================================
雇员ID  姓氏    名字    职务    尊称    地址    
===========================================
1   张  颖  销售代表    女士    复兴门 245 号
2   王  伟  副总裁(销售)    博士    罗马花园 890 号
3   李  芳  销售代表    女士    芍药园小区 78 号
4   郑  建杰    销售代表    先生    前门大街 789 号
5   赵  军  销售经理    先生    学院路 78 号
6   孙  林  销售代表    先生    阜外大街 110 号
7   金  士鹏    销售代表    先生    成府路 119 号
8   刘  英玫    内部销售协调员  女士    建国门 76 号
9   张  雪眉    销售代表    女士    永安路 678 号


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

*** 第 5 个记录集 *** 已打开
===========================================
雇员ID  尊称    地址    
===========================================
4   先生    前门大街 789 号
5   先生    学院路 78 号
6   先生    阜外大街 110 号
7   先生    成府路 119 号


*** 总共有 5 个记录集 ***
True

在数据库窗口中打开spSample存储过程，会出现“存储过程执行成功但未返回记录。”的提示。

 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")

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

*** 第 1 个记录集 *** 已打开
===========================================
雇员ID  姓氏    名字    职务    尊称    地址    
===========================================
1   张  颖  销售代表    女士    复兴门 245 号
2   王  伟  副总裁(销售)    博士    罗马花园 890 号
3   李  芳  销售代表    女士    芍药园小区 78 号
4   郑  建杰    销售代表    先生    前门大街 789 号
5   赵  军  销售经理    先生    学院路 78 号
6   孙  林  销售代表    先生    阜外大街 110 号
7   金  士鹏    销售代表    先生    成府路 119 号
8   刘  英玫    内部销售协调员  女士    建国门 76 号
9   张  雪眉    销售代表    女士    永安路 678 号


*** 第 2 个记录集 *** 已打开
===========================================
雇员ID  尊称    地址    
===========================================
4   先生    前门大街 789 号
5   先生    学院路 78 号
6   先生    阜外大街 110 号
7   先生    成府路 119 号


*** 总共有 2 个记录集 ***
True

再在数据库窗口中打开spSample存储过程，会看到下面的结果：

spSample

雇员ID	姓氏	名字	职务	尊称	地址
1	张	颖	销售代表	女士	复兴门 245 号
2	王	伟	副总裁(销售)	博士	罗马花园 890 号
3	李	芳	销售代表	女士	芍药园小区 78 号
4	郑	建杰	销售代表	先生	前门大街 789 号
5	赵	军	销售经理	先生	学院路 78 号
6	孙	林	销售代表	先生	阜外大街 110 号
7	金	士鹏	销售代表	先生	成府路 119 号
8	刘	英玫	内部销售协调员	女士	建国门 76 号
9	张	雪眉	销售代表	女士	永安路 678 号

 二、分析

在原始示例中，总共产生了 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 注释或删除，以免影响正常的输出。