Récupérer des valeurs dans SQL Server procédures stockées avec ADO

  • Article

Cet article explique comment récupérer des valeurs dans SQL Server procédures stockées avec ADO.

Version du produit d’origine : SQL Server
Numéro de la base de connaissances d’origine : 194792

Résumé

Il existe des problèmes importants à prendre en compte lorsque vous tentez de récupérer des RAISERROR/PRINT/RETURN valeurs à partir d’SQL Server procédures stockées par le biais d’ActiveX Data Objects (ADO). Voici trois problèmes :

  • RAISERRORles instructions dans SQL Server doivent avoir un niveau de gravité de 11 à 18.

  • Les instructions PRINT dans SQL Server peuvent également remplir la collection d’erreurs ADO. Toutefois, les instructions PRINT sont de niveau de gravité zéro (0). Par conséquent, au moins une RAISERROR instruction est nécessaire dans la procédure stockée pour récupérer une instruction PRINT avec ADO via la collection Errors.

  • Les valeurs RETURN d’une procédure stockée doivent être associées à au moins un jeu de résultats.

Plus d’informations

L’exemple de code suivant illustre la navigation dans la collection Ado Errors pour accéder RAISERROR/PRINT/RETURN aux détails d’une procédure stockée SQL Server retournant plusieurs jeux de résultats :

  1. Collez et exécutez le code suivant dans la fenêtre SQL Server Management Studio (SSMS) après avoir créé la base de données Pubs 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éez un projet standard .EXE en Visual Basic. Form1 est créé par défaut.

  3. Dans le menu Projet, choisissez Références , puis sélectionnez la bibliothèque d’objets de données Microsoft ActiveX.

    Remarque

    Vous devez utiliser ADO version 2.0 ou ultérieure pour que le code fonctionne correctement. Vous pouvez obtenir les derniers composants Microsoft Data Access Components (MDAC) sur le web à l’adresse : MDAC.

  4. Placez un bouton Commande sur le formulaire, puis collez le code suivant dans la section Déclarations générales du formulaire. Vous devrez peut-être 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 de deux à sept pour modifier le nombre d’instructions PRINT et/ou RAISERROR d’instructions générées par la procédure stockée et retournées via ADO. Réexécutez l’exemple de code Visual Basic et notez que les RAISERROR instructions et PRINT sont retournées par le biais de la collection d’erreurs ADO. Modifiez les valeurs pour expérimenter différentes combinaisons d’instructions PRINT/RAISERROR avec différents jeux de résultats. Reportez-vous aux procédures stockées SQL pour obtenir des solutions de contournement spécifiques pour des cas particuliers.

Pour récupérer une valeur RETURN dans ADO avec une procédure stockée, il doit y avoir au moins un jeu de résultats. Pour contourner ce problème, lorsqu’aucun jeu de résultats n’est spécifié (dans l’exemple de code ADO), la procédure stockée exécute une valeur SELECT NULL pour renvoyer un jeu de résultats null à ADO, ce qui remplit la valeur RETURN. En outre, pour contourner la question de ne spécifier aucune RAISERROR instruction et une combinaison d’instructions PRINT , les instructions par défaut RAISERROR sont générées afin de fournir un contexte pour retourner l’instruction PRINT via ADO. Vous devez coder RAISERROR les instructions au format indiqué dans la procédure stockée, car seuls les niveaux de gravité de 11 à 18 sont retournés par le biais de la collection d’erreurs ADO.

References

RAISERROR (Transact-SQL)