Récupération de valeurs dans les procédures stockées SQL Server à l’aide d’ADO


Pour obtenir une version Microsoft Visual Basic .NET de cet article, voir 308049 .

Résumé


Il y a des problèmes importants à prendre en compte lors de la tentative de récupération d’une instruction RAISERROR/PRINT/Return à partir de procédures stockées SQL Server par le biais d’objets ADO (ActiveX Data Objects). Voici trois problèmes :
  • Les instructions RAISERROR dans SQL Server doivent être de niveau de gravité 11-18.
  • Les instructions PRINT dans SQL Server peuvent également remplir la collection erreurs ADO. Toutefois, les instructions PRINT sont de niveau de gravité zéro (0) de sorte qu’il est nécessaire d’avoir au moins une instruction RAISERROR dans la procédure stockée pour récupérer une instruction PRINT avec ADO via la collection Errors.
  • Les valeurs de retour d’une procédure stockée doivent être associées à au moins un ResultSet.

Informations supplémentaires


L’exemple de code suivant illustre la navigation dans la collection erreurs ADO pour accéder aux détails de RAISERROR/PRINT/Return d’une procédure stockée SQL Server renvoyant plusieurs jeux de résultats :
  1. Collez et exécutez le code suivant dans la fenêtre de ISQL_W pour créer la procédure stockée utilisée pour l’exemple ADO à l’étape 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. Créer une norme. Projet EXE dans Visual Basic. Form1 est créé par défaut.
  3. Dans le menu projet, sélectionnez références, puis sélectionnez la bibliothèque objets de données Microsoft ActiveX. Remarque : vous devez utiliser la version 2,0 d’ADO ou une version ultérieure pour que le code fonctionne correctement. Vous pouvez obtenir les derniers composants de MDAC sur le Web à l’adresse suivante :
  4. Placez un bouton de commande sur le formulaire, puis collez le code suivant dans la section déclarations générales du formulaire : Remarque : il est possible que vous deviez modifier la chaîne de connexion de base de données pour votre environnement.
          '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. Modifiez la valeur des paramètres deux à sept pour modifier le nombre d’instructions PRINT et/ou d’instructions RAISERROR générées par la procédure stockée et renvoyées par le biais d’ADO. Exécutez de nouveau l’exemple de code Visual Basic et notez que les instructions RAISERROR et PRINT sont renvoyées par le biais de la collection erreurs ADO. Modifiez les valeurs pour tester différentes combinaisons d’instructions PRINT/RAISERROR avec des jeux de résultats différents. Reportez-vous aux procédures stockées SQL pour des solutions de contournement spécifiques pour les cas particuliers. Remarque : pour récupérer une valeur de retour dans ADO avec une procédure stockée, il doit y avoir au moins un ResultSet. Pour contourner ce problème, lorsque aucun jeu de résultats n’est spécifié (dans l’exemple de code ADO), la procédure stockée exécute une sélection de type NULL pour renvoyer un objet ResultSet nul, qui remplit ainsi la valeur de retour. Par ailleurs, pour contourner le problème de spécification d’aucune instruction RAISERROR et d’une combinaison d’instructions PRINT, des instructions RAISERROR par défaut sont générées afin de fournir un contexte permettant de renvoyer l’instruction PRINT par le biais d’ADO. Vous devez coder les instructions RAISERROR dans le format affiché dans la procédure stockée car seuls les niveaux de gravité de 11-18 sont renvoyés par le biais de la collection erreurs ADO.

Références


Aide de Transact-SQL : Rechercher sur : « instruction PRINT/RAISERROR » pour plus d’informations, reportez-vous à l’article suivant dans la base de connaissances Microsoft :
190988 Ouverture de recordsets ADO de manière asynchrone à l’aide de WithEvents