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

[アーティクル]
06/20/2023

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

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

概要

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

RAISERRORSQL Serverのステートメントの重大度レベルは 11 ~ 18 である必要があります。
SQL Serverの PRINT ステートメントでは、ADO エラーコレクションを設定することもできます。ただし、PRINT ステートメントは重大度レベル 0 (0) であるため、Errors コレクションを使用して ADO を使用して PRINT ステートメントを取得するには、ストアドプロシージャで少なくとも 1 つの RAISERROR ステートメントが必要です。
ストアドプロシージャ内の RETURN 値は、少なくとも 1 つの結果セットに関連付ける必要があります。

詳細情報

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

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

Visual Basic で Standard .EXE プロジェクトを作成します。 Form1 は既定で作成されます。
[プロジェクト] メニューの [ 参照 ] を選択し、Microsoft ActiveX データオブジェクトライブラリを選択します。

注:

コードを正しく動作させるには、ADO バージョン 2.0 以降を使用する必要があります。 Web 上の最新の Microsoft Data Access Components (MDAC) コンポーネントは、MDAC で入手できます。

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

'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

ストアドプロシージャによって生成され、ADO 経由で返されるステートメントやRAISERRORステートメントのPRINT数を変更するには、パラメーター 2 ~ 7 の値を変更します。 Visual Basic コードサンプルをもう一度実行し、AND ステートメントが ADO エラーコレクションを通じて返されることに注意してくださいRAISERRORPRINT。値を変更して、異なる結果セットを持つさまざまなステートメントの PRINT/RAISERROR 組み合わせを試してください。特殊な場合の特定の回避策については、SQL ストアドプロシージャを参照してください。

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

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

概要

詳細情報

関連情報

フィードバック

フィードバック

その他のリソース

その他のリソース

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

概要

詳細情報

関連情報

フィードバック

フィードバック

その他のリソース

その他のリソース

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