Abrufen von Werten in SQL Server gespeicherten Prozeduren mit ADO

  • Artikel

In diesem Artikel wird gezeigt, wie Sie Werte in SQL Server gespeicherten Prozeduren mit ADO abrufen.

Ursprüngliche Produktversion: SQL Server
Ursprüngliche KB-Nummer: 194792

Zusammenfassung

Beim Versuch, Werte aus SQL Server gespeicherten Prozeduren über ActiveX Data Objects (ADO) abzurufenRAISERROR/PRINT/RETURN, sind wichtige Probleme zu berücksichtigen. Dies sind drei Probleme:

  • RAISERROR-Anweisungen in SQL Server muss der Schweregrad 11 bis 18 aufweisen.

  • PRINT-Anweisungen in SQL Server können auch die ADO-Fehlerauflistung auffüllen. Print-Anweisungen weisen jedoch den Schweregrad Null (0) auf, sodass mindestens eine RAISERROR Anweisung in der gespeicherten Prozedur erforderlich ist, um eine PRINT-Anweisung mit ADO über die Errors-Auflistung abzurufen.

  • RETURN-Werte in einer gespeicherten Prozedur müssen mindestens einem Resultset zugeordnet sein.

Weitere Informationen

Das folgende Codebeispiel veranschaulicht das Durchsuchen der ADO Errors-Auflistung, um auf die RAISERROR/PRINT/RETURN Details aus einer SQL Server gespeicherten Prozedur zuzugreifen, die mehrere Resultsets zurückgibt:

  1. Fügen Sie den folgenden Code in das SSMS-Fenster (SQL Server Management Studio) ein, nachdem Sie die Pubs-Datenbank erstellt haben, um die gespeicherte Prozedur zu erstellen, die für das ADO-Beispiel in Schritt 4 verwendet wird:

    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. Erstellen Sie ein Standard .EXE-Projekt in Visual Basic. Form1 wird standardmäßig erstellt.

  3. Wählen Sie im Menü Projekt die Option Verweise und dann die Microsoft ActiveX-Datenobjektbibliothek aus.

    Hinweis

    Sie müssen ADO Version 2.0 oder höher verwenden, damit der Code ordnungsgemäß funktioniert. Sie können die neuesten MDAC-Komponenten (Microsoft Data Access Components) im Web unter MDAC abrufen.

  4. Platzieren Sie eine Befehlsschaltfläche auf dem Formular, und fügen Sie dann den folgenden Code in den Abschnitt Allgemeine Deklarationen des Formulars ein. Möglicherweise müssen Sie die Datenbankverbindungszeichenfolge für Ihre Umgebung ändern.

    '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. Ändern Sie den Wert der Parameter 2 bis sieben, um die Anzahl der PRINT Anweisungen und/oder RAISERROR Anweisungen zu ändern, die von der gespeicherten Prozedur generiert und über ADO zurückgegeben werden. Führen Sie das Visual Basic-Codebeispiel erneut aus, und beachten Sie, dass die RAISERROR Anweisungen und PRINT über die ADO-Fehlerauflistung zurückgegeben werden. Ändern Sie die Werte, um mit verschiedenen Kombinationen von PRINT/RAISERROR Anweisungen mit unterschiedlichen Resultsets zu experimentieren. Spezifische Problemumgehungen für Sonderfälle finden Sie in den gespeicherten SQL-Prozeduren.

Um einen RETURN-Wert in ADO mit einer gespeicherten Prozedur abzurufen, muss mindestens ein Resultset vorhanden sein. Um dieses Problem zu umgehen, führt die gespeicherte Prozedur, wenn keine Resultsets angegeben sind (im ADO-Beispielcode), eine SELECT NULL aus, um ein NULL-Resultset an ADO zurückzugeben, wodurch der RETURN-Wert aufgefüllt wird. Darüber hinaus werden Standardanweisungen RAISERROR generiert, um das Problem der Angabe von keine RAISERROR -Anweisungen und einer Kombination von PRINT -Anweisungen zu umgehen, um einen Kontext für die Rückgabe der PRINT Anweisung über ADO bereitzustellen. Sie müssen Anweisungen RAISERROR im formatieren, das in der gespeicherten Prozedur angezeigt wird, da nur die Schweregrade 11-18 durch die ADO-Fehlerauflistung zurückgegeben werden.

References

RAISERROR (Transact-SQL)