Recuperar valores en procedimientos almacenados de SQL Server con ADO

En este artículo se muestra cómo recuperar valores en procedimientos almacenados de SQL Server con ADO.

Versión del producto original:   SQL Server
Número de KB original:   194792

Resumen

Hay aspectos importantes que deben tenerse en cuenta al intentar recuperar RAISERROR/PRINT/RETURN valores de procedimientos almacenados de SQL Server a través de ActiveX Data Objects (ADO). Estos son tres problemas:

  • RAISERROR las instrucciones de SQL Server deben tener un nivel de gravedad de 11-18.

  • Las instrucciones PRINT de SQL Server también pueden rellenar la colección Errors de ADO. Sin embargo, las instrucciones PRINT tienen un nivel de gravedad cero (0), por lo que se requiere al menos una RAISERROR instrucción en el procedimiento almacenado para recuperar una instrucción PRINT con ADO a través de la colección Errors.

  • Los valores devueltos en un procedimiento almacenado deben asociarse con al menos un conjunto de resultados.

Más información

El siguiente ejemplo de código muestra cómo examinar la colección Errors de ADO para obtener acceso a los RAISERROR/PRINT/RETURN detalles de un procedimiento almacenado de SQL Server que devuelve varios conjuntos de elementos:

  1. Pegue y ejecute el siguiente código en la ventana de SQL Server Management Studio (SSMS) después de crear la base de datos pubs para crear el procedimiento almacenado usado para el ejemplo de ADO en el paso 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. Cree un estándar. Proyecto EXE en Visual Basic. Form1 se crea de forma predeterminada.

  3. En el menú proyecto, elija referencias y seleccione la biblioteca Microsoft ActiveX Data Objects.

    Nota

    Debe usar la versión 2,0 o posterior de ADO para que el código funcione correctamente. Puede obtener los últimos componentes de Microsoft Data Access Components (MDAC) en la web en: MDAC.

  4. Ponga un botón de comando en el formulario y, a continuación, pegue el siguiente código en la sección declaraciones generales del formulario. Es posible que deba cambiar la cadena de conexión de base de datos para su entorno.

    '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. Cambie el valor de los parámetros dos a siete para modificar el número de PRINT instrucciones o RAISERROR instrucciones generadas por el procedimiento almacenado y devueltas a través de ADO. Ejecute de nuevo el ejemplo de código de Visual Basic y tenga en cuenta que las RAISERROR PRINT instrucciones y se devuelven a través de la colección Errors de ADO. Cambie los valores para experimentar con diferentes combinaciones de PRINT/RAISERROR instrucciones con conjuntos de cambios diferentes. Consulte los procedimientos almacenados de SQL para obtener soluciones alternativas específicas para casos especiales.

Para recuperar un valor devuelto en ADO con un procedimiento almacenado, debe haber al menos un ResultSet. Para solucionar este problema, cuando no se especifica ningún conjunto de resultados (en el código de ejemplo de ADO), el procedimiento almacenado ejecuta un valor SELECT NULL para devolver un ResultSet nulo a ADO por lo tanto, rellenando el valor devuelto. Además, para evitar el problema de especificar no hay RAISERROR instrucciones y una combinación de instrucciones PRINT , RAISERROR se generan instrucciones default para proporcionar un contexto para devolver la PRINT instrucción a través de ADO. Es necesario codificar RAISERROR las instrucciones en el formato que se muestra en el procedimiento almacenado, ya que solo los niveles de gravedad de 11-18 devuelven la colección Errors de ADO.

Referencias

RAISERROR (Transact-SQL)