ADO を使用してSQL Serverストアド プロシージャの値を取得する

この記事では、ADO を使用してストアド プロシージャSQL Server取得する方法を示します。

元の製品バージョン:  SQL Server
元の KB 番号:   194792

概要

データ オブジェクト (ADO) を使用してストアド プロシージャからSQL Serverを取得ActiveX重要な問題 RAISERROR/PRINT/RETURN があります。 次の 3 つの問題を示します。

  • RAISERRORステートメントはSQL Server 11 ~ 18 の重大度レベルである必要があります。

  • また、ADO エラー コレクションSQL Serverの PRINT ステートメントを設定できます。 ただし、PRINT ステートメントは重大度レベル 0 (0) なので、Errors コレクションを介して ADO を持つ PRINT ステートメントを取得するには、ストアド プロシージャで少なくとも 1 つのステートメントが RAISERROR 必要です。

  • ストアド プロシージャ内の RETURN 値は、少なくとも 1 つの結果セットに関連付けられている必要があります。

詳細情報

次のコード サンプルでは、ADO Errors コレクションを参照して、複数の結果セットを返すストアド プロシージャSQL Server詳細に RAISERROR/PRINT/RETURN アクセスする方法を示します。

  1. Pubs データベースを作成した後、SQL Server Management Studio (SSMS) ウィンドウに次のコードを貼り付け、実行して、手順 4 の ADO サンプルに使用するストアド プロシージャを作成します。

    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. 標準のプロジェクト.EXE作成Visual Basic。 Form1 は既定で作成されます。

  3. [データ オブジェクトProject] メニューの [参照] 選択し、[Microsoft データ ActiveXライブラリ] を選択します。

    注意

    コードが正しく動作するには、ADO バージョン 2.0 以降を使用する必要があります。 Web 上の最新の Microsoft データ アクセス コンポーネント (MDAC) コンポーネントは、次の MDAC で取得できます

  4. [フォーム] に [コマンド] ボタンを配置し、フォームの [全般宣言] セクションに次のコードを貼り付けます。 環境のデータベース接続文字列を変更する必要がある場合があります。

    '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. パラメーター 2 から 7 の値を変更して、ストアド プロシージャによって生成され、ADO によって返されるステートメントの数 PRINT RAISERROR を変更します。 もう一度Visual Basicコード サンプルを実行し、aDO errors コレクションを介して and ステートメント RAISERROR PRINT が返されます。 異なる結果セットを持つステートメントの異なる組み合わせを試 PRINT/RAISERROR す値を変更します。 特殊なケースについてはSQLの回避策については、次のストアド プロシージャを参照してください。

ストアド プロシージャを使用して ADO で RETURN 値を取得するには、少なくとも 1 つの結果セットが必要です。 この問題を回避するために、(ADO サンプル コードで) 結果セットが指定されていない場合、ストアド プロシージャは SELECT NULL を実行して NULL 結果セットを ADO に返し、これにより RETURN 値を設定します。 さらに、ステートメントとステートメントの組み合わせを指定しないという問題を回避するために、ADO を介してステートメントを返すコンテキストを提供するために、既定のステートメントが RAISERROR PRINT RAISERROR PRINT 生成されます。 重大度レベル 11 ~ 18 だけが ADO errors コレクションを通じて返されるので、ストアド プロシージャに示す形式でステートメントを記述 RAISERROR する必要があります。

関連情報

RAISERROR (Transact-SQL)