【ExcelVBA】ExcelのマクロからSQLServerのストアドプロシージャに引数を渡して実行するには

この記事では、ExcelのマクロからSQLServerのストアドプロシージャに引数を渡して実行する方法についてご説明します。

【動画】ExcelのマクロからSQLServerのストアドプロシージャに引数を渡して実行する実際の動き

本題に入る前に、まずはExcelのマクロからSQLServerのストアドプロシージャに引数を渡して実行する実際の動きについて、次のツイートをご覧ください。

マクロ側からSQL Serverのストアドを呼び出す処理を作ってみました😃
ストアドは更新系の処理を実行(更新・追加・削除)😊
ちなみにマクロ側から、ストアド内の処理に必要な引数をストアドに渡しています😀
なお、マクロ側はSQL文を用意していません😅#Excel #vba #SQLServer pic.twitter.com/slSiUpIVlz

— まさ@Excel、VBA、マクロ（経験年数16年） (@masamasa9785) March 29, 2021

ツイート内の動画では、ExcelのマクロからSQLServerのストアドプロシージャに引数を渡して実行しています。

目次へ戻る

ExcelのマクロからSQLServerのストアドプロシージャに引数を渡して実行する方法

ExcelのマクロからSQLServerのストアドプロシージャに引数を渡して実行するには、次の流れの通りにコードを書いていきます。

STEP.1

DBへの接続情報取得

ストアドプロシージャを実行するにはまずはDBに接続しなければなりませんが、接続する情報(ユーザ名やパスワードといった接続情報)がなければDBに接続できません。
なので、最初にDBに接続するための接続情報を取得します。

STEP.2

DBへの接続

接続情報が取得出来たら、その接続情報をもとにDBに接続します。

STEP.3

ストアドプロシージャに渡す引数設定用のインスタンスの生成

ストアドプロシージャに渡す引数設定用のインスタンスを生成します。
このインスタンスは、引数に関する様々な情報(引数の名称や値、型など)を設定するのに必要です。

STEP.4

ストアドプロシージャに渡す引数の様々な情報(引数の名称や値、型など)の取得および設定

ストアドプロシージャに渡す引数について、様々な情報(引数の名称や値、型など)を取得します。
取得した値は、Step3で生成したインスタンスの各プロパティに設定します。

STEP.5

ストアドプロシージャの実行

ストアドプロシージャを実行します。

目次へ戻る

コードの例

Excelのマクロのコード(例)

Excelのマクロのコード(例)は次の通りです。

    Dim cnt As Integer
    Dim DBName As String, connDB As String
    Dim objConnection As ADODB.Connection
    Dim objCommand As ADODB.Command
    Dim objParameter As ADODB.Parameter

    'データベース接続情報を取得
    DBName = "workDB"
    connDB = "Provider=SQLNCLI11.1;"
    connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;"
    connDB = connDB & "Initial Catalog=" & DBName & ";"
    connDB = connDB & "Trusted_Connection=yes;"

    'DBへの接続
    Set objConnection = New ADODB.Connection
    objConnection.CursorLocation = adUseClient
    objConnection.Open connDB

    '実行するストアドプロシージャを設定
    Set objCommand = New ADODB.Command
    Set objCommand.ActiveConnection = objConnection
    objCommand.CommandType = adCmdStoredProc
    objCommand.CommandText = "dbo.sp_dataEdit"

    'ストアドプロシージャの引数に渡す値の設定(渡したい引数の数だけ繰り返す)
    For cnt = 0 To UBound(prmValAry(0), 1)

        'パラメタオブジェクトの作成
        Set objParameter = objCommand.CreateParameter()

        '引数の名前
        objParameter.Name = prmValAry(0)(cnt)

        'データ型の指定(ここでは文字列を指定)
        objParameter.Type = adWChar

        '文字列のサイズの指定
        objParameter.Size = Len(prmValAry(1)(cnt))

        'ストアドプロシージャの引数に渡す値
        objParameter.Value = prmValAry(1)(cnt)

        '入力パラメータ
        objParameter.Direction = adParamInput

        'ストアドプロシージャの引数に渡す値を設定する
        objCommand.Parameters.Append objParameter

    Next
    
    'ストアドを実行
    objCommand.Execute

    '終了処理
    Set objParameter = Nothing
    Set objCommand = Nothing
    Set objConnection = Nothing

コードの解説

最初に見て頂きたいのは29行目から47行目です。

ストアドプロシージャに渡す引数について、様々な情報(引数の名称や値、型など)をストアドプロシージャに渡す引数設定用のインスタンスの各プロパティに設定しています。

次に見て頂きたいのは52行目です。

Executeメソッドでストアドプロシージャを実行しています。

目次へ戻る

ストアドプロシージャのコード(例)も見てみましょう

ストアドプロシージャのコード(例)は次の通りです。

ALTER PROCEDURE [dbo].[sp_dataEdit]
(
	@memID			as NVARCHAR(10),	/* 会員番号       */
	@memName		as NVARCHAR(50),	/* 会員の名前     */
	@postalCode		as NVARCHAR(10),	/* 郵便番号       */
	@addrs			as NVARCHAR(50),	/* 住所           */
	@phoneNum		as NVARCHAR(20),	/* 電話番号       */
	@birth			as NVARCHAR(10),	/* 生年月日       */
	@mailaddrs		as NVARCHAR(50),	/* メールアドレス */
)	
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRANSACTION

	begin
		Update dbo.tbl_userInfo 
		set 
		 memName		= @memName
		,postalCode		= @postalCode
		,addrs			= @addrs
		,phoneNum		= @phoneNum
		,birth			= @birth
		,mailaddrs		= @mailaddrs
		where memID		= @memID
	end

	COMMIT TRANSACTION

END