Retrieve values in SQL Server Stored Procedures with ADO

This article shows how to retrieve values in SQL Server Stored Procedures with ADO.

Original product version:   SQL Server
Original KB number:   194792

Summary

There are important issues to consider when attempting to retrieve RAISERROR/PRINT/RETURN values from SQL Server stored procedures through ActiveX Data Objects (ADO). Here are three issues:

  • RAISERROR statements in SQL Server must be a severity level of 11-18.

  • PRINT statements in SQL Server can also populate the ADO errors collection. However, PRINT statements are severity level zero (0) so, at least one RAISERROR statement is required in the stored procedure to retrieve a PRINT statement with ADO through the Errors collection.

  • RETURN values in a stored procedure must be associated with at least one resultset.

More information

The following code sample demonstrates browsing the ADO Errors collection to access the RAISERROR/PRINT/RETURN detail from a SQL Server stored procedure returning multiple resultsets:

  1. Paste and execute the following code in the SQL Server Management Studio (SSMS) window after creating Pubs database to create the stored procedure used for the ADO sample in step 4:

    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. Create a Standard .EXE project in Visual Basic. Form1 is created by default.

  3. From the Project menu, choose References and select the Microsoft ActiveX Data Objects Library.

    Note

    You must use ADO version 2.0 or later for the code to work correctly. You can obtain the latest Microsoft Data Access Components (MDAC) components on the Web at: MDAC.

  4. Place a Command button on the Form, and then paste the following code in the General Declarations section of the Form. You may need to change the database connect string for your environment.

    '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. Change the value of parameters two through seven to alter the number of PRINT statements and/or RAISERROR statements generated by the stored procedure and returned through ADO. Run the Visual Basic code sample again and note that the RAISERROR and PRINT statements are returned through the ADO errors collection. Change the values to experiment with different combinations of PRINT/RAISERROR statements with different resultsets. Refer to the SQL stored procedures for specific workarounds for special cases.

To retrieve a RETURN value in ADO with a stored procedure, there must be at least one resultset. In order to work around this problem, when no resultsets are specified (in the ADO sample code) the stored procedure executes a SELECT NULL to return a null resultset to ADO thereby populating the RETURN value. In addition, to work around the issue of specifying no RAISERROR statements and a combination of PRINT statements, default RAISERROR statements are generated in order to provide a context for returning the PRINT statement via ADO. You must code RAISERROR statements in the format shown in the stored procedure because only severity levels of 11-18 return through the ADO errors collection.

References

RAISERROR (Transact-SQL)