Cómo recuperar valores en procedimientos almacenados de SQL Server con ADO


Para obtener una versión de Microsoft Visual Basic .NET de este artículo, consulte 308049 .

Resumen


Hay problemas importantes que se deben tener en cuenta al intentar recuperar valores de RAISERROR/PRINT/devueltos de procedimientos almacenados de SQL Server a través de objetos de datos ActiveX (ADO). Estos son tres problemas:
  • Las instrucciones RAISERROR en SQL Server deben ser 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, los Estados de impresión son de nivel de gravedad cero (0), por lo que al menos una instrucción RAISERROR es necesaria en el procedimiento almacenado para recuperar una instrucción PRINT con ADO a través de la colección Errors.
  • Los valores devueltos de un procedimiento almacenado deben asociarse al menos a un conjunto de resultados.

Más información


En el siguiente ejemplo de código se muestra cómo examinar la colección de errores de ADO para obtener acceso al detalle RAISERROR/PRINT/Return 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 ISQL_W para crear el procedimiento almacenado usado para la muestra 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. Crear un estándar. Proyecto EXE en Visual Basic. Se creará Form1 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 componentes de Microsoft Data Access Components (MDAC) más recientes en la web en la siguiente dirección URL:
  4. Coloque un botón de comando en el formulario y, a continuación, pegue el código siguiente en la sección declaraciones generales del formulario: Nota: es posible que tenga que cambiar la cadena de conexión de la base de datos de 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 del dos al siete para modificar el número de instrucciones PRINT o de instrucciones RAISERROR generadas por el procedimiento almacenado y devueltas por ADO. Ejecute el ejemplo de código de Visual Basic de nuevo y observe que las instrucciones RAISERROR e PRINT se devuelven a través de la colección Errors de ADO. Cambie los valores para experimentar con diferentes combinaciones de instrucciones PRINT/RAISERROR con distintos conjuntos de valores. Consulte los procedimientos almacenados de SQL para soluciones alternativas específicas para casos especiales. Nota: para recuperar un valor devuelto en ADO con un procedimiento almacenado, debe haber al menos un conjunto de resultados. Para solucionar este problema, cuando no se especifica ningún ResultSet (en el código de ejemplo de ADO) el procedimiento almacenado ejecuta un valor SELECT NULL para devolver un ResultSet null a ADO, por lo que se rellena el valor devuelto. Además, para evitar el problema de especificar que no se especifiquen instrucciones RAISERROR y una combinación de instrucciones PRINT, se generan instrucciones RAISERROR predeterminadas para proporcionar un contexto para devolver la instrucción PRINT a través de ADO. Debe codificar las instrucciones RAISERROR en el formato que se muestra en el procedimiento almacenado, porque solo los niveles de gravedad de 11-18 devuelven la colección de errores de ADO.

Referencias


Ayuda de Transact-SQL: buscar: "instrucción PRINT/RAISERROR" para obtener más información, consulte el siguiente artículo de Microsoft Knowledge Base:
190988 Cómo abrir conjuntos de registros ADO de forma asincrónica mediante WithEvents