使用 ADO 检索 SQL Server 存储过程中的值

本文介绍如何使用 ADO 检索 SQL Server 存储过程中的值。

原始产品版本:   SQL Server
原始 KB 数:   194792

摘要

在尝试 RAISERROR/PRINT/RETURN 从 SQL Server 存储过程中检索来自 SQL Server 存储过程的值时,请注意从 ADO) (的 ActiveX 数据对象。 以下是三个问题:

  • RAISERROR SQL Server 中的语句必须为严重级别11-18。

  • SQL Server 中的 PRINT 语句也可以填充 ADO errors 集合。 但是,PRINT 语句的严重级别为零 (0) 因此, RAISERROR 存储过程中至少需要一个语句,以通过 ADO 通过 Errors 集合检索 PRINT 语句。

  • 存储过程中的返回值必须至少与一个结果集相关联。

详细信息

下面的代码示例演示了如何浏览 ADO Errors 集合以访问 RAISERROR/PRINT/RETURN 返回多个结果集的 SQL Server 存储过程中的详细信息:

  1. 创建 Pubs 数据库 后,在 SQL Server Management STUDIO (SSMS) 窗口中粘贴并执行以下代码,以创建在步骤4中用于 ADO 示例的存储过程:

    use pubs
    GO
    
    if exists (select * from sysobjects where id = object_id('dbo.ADOTestRPE') and sysstat & 0xf = 4)
        drop procedure dbo.ADOTestRPE
    GO
    
    create procedure ADOTestRPE
     (
         @SetRtn INT=0 OUTPUT,
         @R1Num INT=1,
         @P1Num INT=1,
         @E1Num INT=1,
         @R2Num INT=2,
         @P2Num INT=2,
         @E2Num INT=2
     )
     AS
     DECLARE @iLoop INT
     DECLARE @PrintText VARCHAR(255)
     DECLARE @iErrNum INT
    
    /* Check for no Resultsets - needed to get the RETURN value back */ 
     IF @R1Num + @R2Num = 0 SELECT NULL
    
    /* Resultset 1 ******************************* */ 
    
    IF @R1Num > 0
        BEGIN
            SET ROWCOUNT @R1Num
            SELECT 'Resultset 1' RsNum, Title
            FROM Pubs..Titles
            SET ROWCOUNT 0
        END
    
    /* Must raise a default error context in which to return the PRINT */ 
     /* statement */ 
     /* (if none present) since PRINT statements are a severity level of */ 
     /*0. */ 
    IF (@P1Num > 0) AND (@E1Num = 0) RAISERROR ("RAISERROR.PError1", 11, 2)
    
    IF @P1Num > 0
        BEGIN
            SELECT @iLoop = 0
            WHILE @iLoop < @P1Num
            BEGIN
            SELECT @iLoop = @iLoop + 1
            SELECT @PrintText = 'PRINT.Resultset.1: Line ' +
            CONVERT(char(2), @iLoop)
            PRINT @PrintText
        END
    END
    
    IF @E1Num > 0
        BEGIN
            SELECT @iLoop = 0
            WHILE @iLoop < @E1Num
            BEGIN
            SELECT @iLoop = @iLoop + 1
            SELECT @iErrNum = @iLoop + 201000
            RAISERROR ("RAISERROR.Resultset.1", 11, 2)
        END
    END
    
    /* Resultset 2 ******************************* */ 
    
    IF @R2Num > 0
        BEGIN
            SET ROWCOUNT @R2Num
            SELECT 'Resultset 2' RsNum, Title
            FROM Pubs..Titles
            SET ROWCOUNT 0
        END
    
    /* Must raise a default error context in which to return the PRINT */ 
    /* statement */ 
    /* (if none present) since PRINT statements are a severity level of */ 
    /* 0. */ 
    IF (@P2Num > 0) AND (@E2Num = 0) RAISERROR ("RAISERROR.PError2",11, 2)
    
    IF @P2Num > 0
     BEGIN
     SELECT @iLoop = 0
     WHILE @iLoop < @P2Num
     BEGIN
     SELECT @iLoop = @iLoop + 1
     SELECT @PrintText = 'PRINT.Resultset.2: Line ' +
     CONVERT(char(2), @iLoop)
     PRINT @PrintText
     END
     END
    
    IF @E2Num > 0
        BEGIN
            SELECT @iLoop = 0
            WHILE @iLoop < @E2Num
            BEGIN
            SELECT @iLoop = @iLoop + 1
    
            SELECT @iErrNum = @iLoop + 202000
            RAISERROR ("RAISERROR.Resultset.2", 11, 2)
        END
    END
    
    /* Return & Output ************************************ */ 
    
    select @SetRtn = -1
    RETURN @SetRtn
    GO
    
    
  2. 创建标准。EXE 项目在 Visual Basic 中。 默认情况下会创建 Form1。

  3. 从 "项目" 菜单中,选择 " 引用 " 并选择 "Microsoft ActiveX 数据对象库"。

    备注

    您必须使用 ADO 版本2.0 或更高版本,代码才能正常工作。 您可以通过以下网站获取 (MDAC) 组件的最新 Microsoft 数据访问组件: mdac

  4. 将命令按钮放置在窗体上,然后将以下代码粘贴到窗体的 "通用声明" 部分。 您可能需要更改环境的数据库连接字符串。

    'This Code demonstrates RAISERROR/PRINT/RETURN values with ADO and
    'multiple resultsets.
    
    Sub CreateParms()
    
    Dim ADOCmd As New ADODB.Command
    Dim ADOPrm As New ADODB.Parameter
    Dim ADOCon As ADODB.Connection
    Dim ADORs As ADODB.Recordset
    Dim sParmName As String
    Dim strConnect As String
    Dim rStr As String
    
    On Error GoTo ErrHandler
    
    strConnect = "driver={SQL
    Server};server=(local);uid=sa;pwd=;database=pubs"
    
    Set ADOCon = New ADODB.Connection
    With ADOCon
    .Provider = "MSDASQL"
    .CursorLocation = adUseServer 'Must use Server side cursor.
    .ConnectionString = strConnect
    .Open
    End With
    
    Set ADOCmd.ActiveConnection = ADOCon
    With ADOCmd
    .CommandType = adCmdStoredProc
    .CommandText = "ADOTestRPE"
    End With
    
    'Parameter 0 is the stored procedure Return code.
    sParmName = "Return"
    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
    adParamReturnValue, , 0)
    ADOCmd.Parameters.Append ADOPrm
    ADOCmd.Parameters(sParmName).Value = -1
    
    'Parameter 1 is the setting for the stored procedure Output
    ' parameter.
    sParmName = "Output"
    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
    adParamOutput)
    ADOCmd.Parameters.Append ADOPrm
    ADOCmd.Parameters(sParmName).Value = 999
    
    'Parameter 2
    sParmName = "R1Num" 'Number of rows to return in Resultset 1.
    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
    adParamInput)
    ADOCmd.Parameters.Append ADOPrm
    ADOCmd.Parameters(sParmName).Value = 1
    
    'Parameter 3
    sParmName = "P1Num" 'Number of PRINT statements in Resultset 1.
    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
    adParamInput)
    ADOCmd.Parameters.Append ADOPrm
    ADOCmd.Parameters(sParmName).Value = 0
    
    'Parameter 4
    sParmName = "E1Num" 'Number of RAISERROR statements in Resultset
    '1.
    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
    adParamInput)
    ADOCmd.Parameters.Append ADOPrm
    ADOCmd.Parameters(sParmName).Value = 0
    
    'Parameter 5
    sParmName = "R2Num" 'Number of rows to return in Resultset 2.
    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
    adParamInput)
    ADOCmd.Parameters.Append ADOPrm
    ADOCmd.Parameters(sParmName).Value = 2
    
    'Parameter 6
    sParmName = "P2Num" 'Number of PRINT statements in Resultset 2.
    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
    adParamInput)
    ADOCmd.Parameters.Append ADOPrm
    ADOCmd.Parameters(sParmName).Value = 0
    
    'Parameter 7
    sParmName = "E2Num" 'Number of RAISERROR statements in Resultset
    ' 2.
    Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
    adParamInput)
    ADOCmd.Parameters.Append ADOPrm
    ADOCmd.Parameters(sParmName).Value = 0
    
    Set ADORs = ADOCmd.Execute
    
    Do While (Not ADORs Is Nothing)
        If ADORs.State = adStateClosed Then Exit Do
        While Not ADORs.EOF
            For i = 0 To ADORs.Fields.Count - 1
            rStr = rStr & " : " & ADORs(i)
        Next i
        Debug.Print Mid(rStr, 3, Len(rStr))
        ADORs.MoveNext
        rStr = ""
        Wend
        Debug.Print "----------------------"
        Set ADORs = ADORs.NextRecordset
    Loop
    
    Debug.Print "Return: " & ADOCmd.Parameters("Return").Value
    Debug.Print "Output: " & ADOCmd.Parameters("Output").Value
    
    GoTo Shutdown
    
    ErrHandler:
    Call ErrHandler(ADOCon)
    Resume Next
    
    Shutdown:
    Set ADOCmd = Nothing
    Set ADOPrm = Nothing
    Set ADORs = Nothing
    Set ADOCon = Nothing
    
    End Sub
    
    Private Sub Command1_Click()
    
    Call CreateParms
    
    End Sub
    
    Sub ErrHandler(objCon As Object)
    
    Dim ADOErr As ADODB.Error
    Dim strError As String
    
    For Each ADOErr In objCon.Errors
        strError = "Error #" & ADOErr.Number & vbCrLf & ADOErr.Description _
        & vbCr & _
        " (Source: " & ADOErr.Source & ")" & vbCr & _
        " (SQL State: " & ADOErr.SQLState & ")" & vbCr & _
        " (NativeError: " & ADOErr.NativeError & ")" & vbCr
        If ADOErr.HelpFile = "" Then
            strError = strError & " No Help file available" & vbCr & vbCr
        Else
            strError = strError & " (HelpFile: " & ADOErr.HelpFile & ")" _
            & vbCr & _
            " (HelpContext: " & ADOErr.HelpContext & ")" & _
            vbCr & vbCr
        End If
        Debug.Print strError
    Next
    
    objCon.Errors.Clear
    
    End Sub
    
  5. 将参数值从2更改为7,以更改 PRINT RAISERROR 由存储过程生成并通过 ADO 返回的语句和/或语句的数目。 再次运行 Visual Basic 代码示例,并请注意 RAISERROR ,和 PRINT 语句通过 ADO errors 集合返回。 使用不同的结果更改值以试验不同的 PRINT/RAISERROR 语句组合。 有关特殊情况的具体解决方法,请参阅 SQL 存储过程。

若要使用存储过程检索 ADO 中的返回值,必须至少有一个结果集。 为了解决此问题,在 ADO 示例代码中未指定任何结果集时 () 存储过程执行 SELECT NULL 以将 null 结果集返回给 ADO,从而填充返回值。 此外,若要解决指定无 RAISERROR 语句和语句组合的问题 PRINT ,则将生成默认语句,以便 RAISERROR 通过 ADO 提供用于返回语句的上下文 PRINT 。 您必须 RAISERROR 采用存储过程中显示的格式编写代码语句,因为只有11-18 的严重级别通过 ADO errors 集合返回。

参考

RAISERROR (Transact-sql)