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

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

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

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

ツイート内の動画では、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

コードの解説

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

Excelのマクロから渡された引数をこの行で受け取っています。

コード(例)では、受け取った引数を18行目のUpdate文で使い更新処理を行っています。

最後に

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

ExcelのマクロからSQLServerのストアドプロシージャに引数を渡すには、まずはストアドプロシージャに渡す引数設定用のインスタンスを生成し、そのインスタンスの各プロパティに設定する必要があります。

次にExecuteメソッドを実行することでストアドプロシージャが実行されます。

また、ストアドプロシージャ側についてですが、Excelのマクロ側から渡される引数を受け取れるよう、引数格納用の変数を用意しておく必要があります。

引数格納用の変数に漏れがないよう必ず記述するようにしましょう。

漏れがあると正しくストアドプロシージャを実行することができないので気をつけましょうね。

今回ご紹介したコードの例を参考にしていただけたら幸いです。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→ TechAcademyの「1週間 無料体験」はこちら