Abrufen von Werten in gespeicherten SQL Server-Prozeduren mit ADO

2024-12-14

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

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

Zusammenfassung

Beim Versuch, Werte aus gespeicherten SQL Server-Prozeduren über ActiveX Data Objects (ADO) abzurufen RAISERROR/PRINT/RETURN , müssen wichtige Probleme berücksichtigt werden. Hier sind drei Probleme:

RAISERROR Anweisungen in SQL Server müssen schweregrad 11-18 sein.
PRINT-Anweisungen in SQL Server können auch die ADO-Fehlerauflistung auffüllen. PRINT-Anweisungen sind jedoch der Schweregrad Null (0), daher ist mindestens eine RAISERROR Anweisung in der gespeicherten Prozedur erforderlich, um eine PRINT-Anweisung mit ADO über die Errors-Auflistung abzurufen.
RÜCKGABEwerte in einer gespeicherten Prozedur müssen mindestens einem Resultset zugeordnet sein.

Weitere Informationen

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

Fügen Sie den folgenden Code in das Fenster SQL Server Management Studio (SSMS) ein, und führen Sie ihn aus, 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

Erstellen Sie ein Standard-.EXE Projekt in Visual Basic. Formular1 wird standardmäßig erstellt.
Wählen Sie im Menü "Projekt" die Option "Verweise " und dann die Microsoft ActiveX-Datenobjektbibliothek aus.

Notiz

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

Platzieren Sie eine Befehlsschaltfläche auf dem Formular, und fügen Sie dann den folgenden Code im 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

Ändern Sie den Wert von Parametern 2 bis sieben, um die Anzahl von 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 Und-Anweisungen 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. In den gespeicherten SQL-Prozeduren finden Sie spezielle Problemumgehungen für Sonderfälle.

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 eine SELECT NULL aus, wenn keine Resultsets angegeben werden (im ADO-Beispielcode), um ein NULL-Resultset an ADO zurückzugeben, wodurch der RÜCKGABEwert auffüllt wird. Darüber hinaus werden Standardanweisungen RAISERROR generiert, um das Problem zu umgehen, keine RAISERROR Anweisungen und eine Kombination von PRINT Anweisungen anzugeben, um einen Kontext für die Rückgabe der PRINT Anweisung über ADO bereitzustellen. Sie müssen Codeanweisungen RAISERROR im formatieren, das in der gespeicherten Prozedur angezeigt wird, da nur schweregrade von 11-18 durch die ADO-Fehlerauflistung zurückgegeben werden.

References

RAISERROR (Transact-SQL)

Freigeben über

Abrufen von Werten in gespeicherten SQL Server-Prozeduren mit ADO

Zusammenfassung

Weitere Informationen

References

Feedback

Zusätzliche Ressourcen