【Excel・Access VBA】ADOでストアドを実行してSELECT結果を受け取る方法

VBA
2021.10.18
スポンサーリンク

今回の記事では、VBAからADOを介してSQL Serverのストアドを実行し、ストアド内でSELECTした表をVBA側に返す一連の処理のサンプルプログラムを紹介します。
恐らくSQL Server以外のRDBMSで作成したストアドでも使えるとは思いますが、SQL Server以外は未検証です。

尚、以前の記事で、VBAからADOを介してストアドを実行して、その実行結果を受け取る処理のサンプルコードも紹介しています。
ストアド内で実行したSELECT結果を受け取るのではなく、ストアドを実行してRETURNの値を受け取りたい場合は以下の記事を参考にしてください。

【Excel・Access VBA】ADOでストアドを実行するサンプルプログラム
今回はVBAからADO経由でデータベースサーバーのストアドプロシージャを実行する方法を紹介します。 尚、以前にVB...
www.depthbomb.net
2020.06.20

 
 

事前準備：DB接続用クラスの実装

過去の記事で、VBAでADOを使用してデータベースに接続する処理をクラス化したサンプルプログラムを紹介させていただいており、今回の記事では、そのクラスに対して、ストアドを実行してSELECT結果を受け取るメソッドを追加します。
そのため、以下の記事で紹介しているクラスをVBAのプロジェクトに追加してください。

【Excel・Access VBA】データベース接続用クラスで時短プログラミング
ExcelやAccessでデータベースに接続してデータ取得やデータ更新処理を実行する場合に、ADOを利用して実装すること...
www.depthbomb.net
2019.11.27

尚、このデータベース接続用クラスを流用するのは、データベースとのConnectionを使いまわすだけの目的であり、Connectionを新しく作るのであれば、こちらのクラスを使用しなくても問題はありません。
 
 

サンプルプログラム

当項では、ストアド側のサンプルプログラムとVBA側のサンプルプログラムをそれぞれ紹介します。
尚、重要なのはVBA側です。
 

ストアド側サンプルプログラム

今回の記事の検証のためにストアドを作成したRDBMSはSQL Serverです。
ストアド側でのポイントは、処理の最後に返したい表のSELECT文を実行しておくことです。
そのSELECT結果を変数に入れるなどの特別な処理は不要です(※SQL Server以外は試していないので不明)。

ストアド側のサンプルコートでは、ストアド名を「uspSelectTest」としています。
必要によって名前を変更して使用してください。

CREATE PROCEDURE [dbo].[uspSelectTest]
	@para1	int
AS
BEGIN

	--なんやかんやの処理開始

	--なんやかんやの処理ここまで

	--クライアントに返す表を作成します。
	SELECT * FROM 対象テーブル WHERE 列名1 = @para1;

	RETURN 0

	--例外処理
	ERR:
		RETURN 1

END

GO

 

VBA側ストアド実行用クラス　サンプルプログラム

前述したデータベース接続用クラスを使用する場合は、そのクラス内に以下のサンプルコードを丸っとコピペして追加してください。

'引数のストアド名とパラメーターを使用して、ストアドを実行します。
'引数1：ストアド名
'引数2：パラメーター※2次元配列(パラメータ名,データ型,値) パラメーターは5つまで。
'戻り値：ストアド内で作成した表を戻り値にセット
Public Function execute_usp_select(SpName As String, Param() As String) As Variant

Dim cmd As New ADODB.Command
Dim Param1 As New ADODB.Parameter
Dim Param2 As New ADODB.Parameter
Dim Param3 As New ADODB.Parameter
Dim Param4 As New ADODB.Parameter
Dim Param5 As New ADODB.Parameter
Dim i As Integer

    
    Set cmd.ActiveConnection = mCon
    'コマンドタイプにストアドを指定します。
    cmd.CommandType = adCmdStoredProc
    'ストアド名をセットします。
    cmd.CommandText = SpName

    '引数のパラメーター配列を要素数分ループします。
    For i = LBound(Param, 1) To UBound(Param, 1)
    
        'パラメータが5以上(カウンタが4以上)の場合はループから抜けます。
        If i > 4 Then
            Exit For
        End If
        
        'パラメーターをセットします。
        Select Case i
            Case 0
                Set Param1 = cmd.CreateParameter(Param(0, 0), Param(0, 1), adParamInput)
                Param1.Value = Param(0, 2)
                cmd.Parameters.Append Param1
            Case 1
                Set Param2 = cmd.CreateParameter(Param(1, 0), Param(1, 1), adParamInput)
                Param2.Value = Param(1, 2)
                cmd.Parameters.Append Param2
            Case 2
                Set Param3 = cmd.CreateParameter(Param(2, 0), Param(2, 1), adParamInput)
                Param3.Value = Param(2, 2)
                cmd.Parameters.Append Param3
            Case 3
                Set Param4 = cmd.CreateParameter(Param(3, 0), Param(3, 1), adParamInput)
                Param4.Value = Param(3, 2)
                cmd.Parameters.Append Param4
            Case 4
                Set Param5 = cmd.CreateParameter(Param(4, 0), Param(4, 1), adParamInput)
                Param5.Value = Param(4, 2)
                cmd.Parameters.Append Param5
        End Select
        
    Next i

	'ストアド内で取得した表を戻り値にセットします。
    Set execute_usp_select = cmd.execute

    Set Param5 = Nothing
    Set Param4 = Nothing
    Set Param3 = Nothing
    Set Param2 = Nothing
    Set Param1 = Nothing

    Set cmd = Nothing

End Function

 
上記の処理は、冒頭でお伝えした、過去に記事にしているVBAのADOからストアドを実行する処理を流用し、わずかに変更して実装しています。
 

VBA側クラス呼び出し　サンプルプログラム

上記のクラスの呼び出し例としてのサンプルプログラムを紹介します。
サンプルコードでは、Subプロシージャ名を「Sample」としています。
こちらも必要によって変更してください。
また、ストアドに渡すパラメーターは配列ですが、コメントにもあるようにパラメーターを増やしたければ、添え字を必要によって変更してください。

Sub Sample()

Dim db As test_project.DataBaseAccess
Dim Rs As ADODB.Recordset
Dim strSQL As String
Dim strSpName As String
Dim strPara(0, 2) As String	'必要により添え字を変更

    Set db = test_project.DatabaseFactory.creater

    'サーバデータベースと接続します。
    db.connect

    strSpName = "dbo.uspSelectTest"

    'パラメーター1
    strPara(0, 0) = "para1"	'パラメーター名
    strPara(0, 1) = adBigInt	'データ型
    strPara(0, 2) = 1111	'値
    'パラメーター2　※必要によってコメントを外して使用してください。
    'strPara(1, 0) = "para2"	'パラメーター名
    'strPara(1, 1) = adBigInt	'データ型
    'strPara(1, 2) = 0		'値

    'データが無ければレコードセットはEOFになります。
    Set Rs = db.execute_select_usp(strSpName, strPara)
    
    Do Until Rs.EOF
        Debug.Print Rs(0).Value & " | " & Rs(1).Value & " | " & Rs(2).Value & " | " & Rs(3).Value & " | " & Rs(4).Value
        Rs.MoveNext
    Loop

    Set rs = Nothing
    db.disconnect
    Set db = Nothing
End Sub

 
この処理では、ストアドを実行して、そのストアド内で実行したSELECT結果をレコードセットオブジェクトで受け取っています。
SELECTした際にレコードが存在しなければ、そのレコードセットはEOFになります。

後はレコードセットなので、ループで回すなりしてレコードを取り出します。
簡単ですね！
 
 
 

【番外編】もう一つのサンプルプログラム

この記事を書いた当初は、ストアドで実行したSELECT結果をVBA側に戻せないと勘違いしていたので、その時に作成したサンプルプログラムも参考に掲載しておきます。

こちらのサンプルでは、冒頭で紹介した、VBAのADOを介してストアドを実行する処理で実装しているストアド実行用メソッドを使用してストアドを実行し、そのストアド内で一時テーブルを作成して、ストアド実行後に同じセッション内でその一時テーブルを参照するといった実装をしています。
一時テーブルの扱い方の参考になるかも知れません。
 
 

【番外編】サンプルプログラムの処理の流れ

今回のサンプルプログラムでは、ストアド側の処理と、VBAでADOを使用してストアドを呼び出す側の処理をそれぞれ紹介します。

ストアド側では、ストアド内で一時テーブルを作成し、その一時テーブルに対してVBA側に返したいデータをSELECTしてINSERTしています。

VBA側では対象のストアドをADOを介して呼び出した後に、同じコネクションのなかでストアド内で作成された一時テーブルをSELECTして、そのSELECT結果をレコードセットに格納しています。
 

一時テーブルを使用したサンプルプログラム

当項では、SQL Server内のストアドで作成した一時テーブルをVBA側のADOで参照させる処理のサンプルプログラムを紹介します。
 

ストアド側のサンプルプログラム

CREATE PROCEDURE [dbo].[uspSelectTest]
  @para1	int
AS
BEGIN

  --なんやかんやの処理開始

  --なんやかんやの処理ここまで

  --クライアントに渡す一時テーブルを作成します。
  CREATE TABLE ##tempテーブル (
  列名1 int,
  列名2 int,
  列名3 int,
  列名4 int,
  列名5 nvarchar(30)

  )
  --一時テーブルにデータを追加します。
  INSERT INTO ##tempテーブル(列名1,列名2,列名3,列名4,列名5)
  SELECT 列名1,列名2,列名3,列名4,列名5
  FROM スキーマ名.対象テーブル名
  WHERE 列名1 = @para1;

  RETURN 0

  --例外処理
  ERR:
    RETURN 1

END

GO

 

処理の前半では、ストアドなので、テーブルの更新やら追加やらなんやらがある想定です。
その後の処理として、11行目で「一時テーブル」を作成しています。

SQL Serverの場合は、テーブル名の先頭に # を付けると一時テーブルとして扱われます。

# が一つだと「ローカル一時テーブル」となり、作成したユーザーのみが参照できます。
# が二つだと「グローバル一時テーブル」となり、作成したユーザー以外でも参照できます。

一時テーブルは、使用していたセッションが切断されると消滅しますが、グローバル一時テーブルの場合は複数のユーザーのセッションして使用されていた場合はそのすべてのユーザーのセッションが切断されてから消滅します。
※本来はローカル一時テーブルを使用するのが望ましいのですが、当方で検証していた結果、ローカル一時テーブルだとVBA側から上手く見つけられなかったため、グローバル一時テーブルを使用しています。ここは時間があれば調べたいところです・・・。

20行目で一時テーブルに対して特定のテーブルのSELECT結果をINSERTしています。
ここでINSERTした一時テーブル内のデータは、このセッションが生きている間は残存します。
 

VBA側のサンプルコード

実行すると、ストアドを呼び出して、ストアド内で作成した一時テーブルを参照します。
その参照結果をレコードセットに格納して、debug.printで出力します。

Sub Sample()

Dim db As test_project.DataBaseAccess
Dim Rs As ADODB.Recordset
Dim strSQL As String
Dim strSpName As String
Dim strPara(0, 2) As String	'必要により添え字を変更
Dim varReturn As Variant

    Set db = test_project.DatabaseFactory.creater

    'サーバデータベースと接続します。
    db.connect

    strSpName = "dbo.uspSelectTest"

    'パラメーター1
    strPara(0, 0) = "para1"	'パラメーター名
    strPara(0, 1) = adBigInt	'データ型
    strPara(0, 2) = 1111	'値
    'パラメーター2　※必要によってコメントを外して使用してください。
    'strPara(1, 0) = "para2"	'パラメーター名
    'strPara(1, 1) = adBigInt	'データ型
    'strPara(1, 2) = 0		'値

  　'パラメーターを渡してストアドを実行します。
    varReturn = db.execute_usp(strSpName, strPara)

    'ストアドの戻り値が0(正常終了)か否かを判定します。
    If varReturn = 0 Then
    
    	'ストアドが作成した一時テーブルをSELECTします。
        strSQL = "SELECT * FROM ##tempテーブル"
        Set Rs = db.execute(strSQL)
        'レコードセットの行数文ループします。
        Do Until Rs.EOF
            Debug.Print Rs(0).Value & " | " & Rs(1).Value & " | " & Rs(2).Value & " | " & Rs(3).Value & " | " & Rs(4).Value
            Rs.MoveNext
        Loop
        
        Set Rs = Nothing
    End If
    
    db.disconnect
    Set db = Nothing

End Sub

3行目と10行目のプロジェクト名は実際に使用するVBAのプロジェクト名に書き変えてください。
27行目で使用しているメソッドは、以前の記事で紹介しているストアド実行用のメソッドです。
33行目からは一時テーブルを指定してSELECT文を実行してその結果をレコードセットに格納しています。
 
 

最後に

当記事は、当ブログへの問い合わせを元に実際に検証して記事に致しました。
いったんはストアドのSELECT結果をVBAで受け取れないと記事を書いてしまい、再度検証してみたら普通に出来てしまって、慌てて記事を修正させていただきました。

今後は間違った内容の記事を公開してしまわないように気を付けます。
結果的には二通りの実装方法を紹介できたのでヨシとします。

今回も読んで頂きましてありがとうございました。
それでは皆さまごきげんよう！

タイトルとURLをコピーしました