この記事では、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のストアドプロシージャに引数を渡して実行するには、次の流れの通りにコードを書いていきます。
なので、最初にDBに接続するための接続情報を取得します。
このインスタンスは、引数に関する様々な情報(引数の名称や値、型など)を設定するのに必要です。
取得した値は、Step3で生成したインスタンスの各プロパティに設定します。
コードの例
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週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。