Excel(VBA)を使用して、SQL Serverのストプロ(引数あり)を実行する

サンプルプログラムの概要

VBAから
　・引数として文字列を渡して
　・ストプロ「SampleProcedure」を実行(引数あり)
します。

ストプロを作成

ここでは例として、
　・引数として文字列をもらって
　・テーブル「tableA」へレコードをINSERTするストプロ「SampleProcedure」
を作成します。

--DB名を設定
USE [sampleDB]
GO

CREATE OR ALTER PROCEDURE SampleProcedure
	--引数
	@message VARCHAR(50)
AS
	--tableAへレコードをINSERT
	INSERT INTO tableA VALUES(@message,GETDATE())
	RETURN
GO

VBAを作成

ここでは例として、
　・上記で作成したストプロ「SampleProcedure」を実行
します。

Option Explicit

Sub execSqlToSqlServer()
    '===============================
    '接続文字列
    '===============================
    'プロバイダ
    Const PROVIDER As String = "MSOLEDBSQL"
    'サーバー名(サーバーのPC名\インスタンス名)
    Const SERVER_NAME As String = "localhost\SQLEXPRESS"
    'DB名
    Const DB_NAME As String = "sampleDB"
    'ユーザー名
    Const USER_NAME As String = "XXXXX"
    'パスワード
    Const PASSWORD As String = "XXXXX"
    'ストプロ名
    Const STORED_PROC As String = "SampleProcedure"
    
    Dim con As Object
    Dim conStr As String
    Dim command As Object
    Dim commandParameter As Object
    Dim errorMessage As String
    
    On Error GoTo conError
    
    '===============================
    'SQL Serverへ接続(Windows認証)
    '===============================
    '接続文字列の組み立て
    conStr = "Provider=" & PROVIDER & ";" & _
              "Data Source='" & SERVER_NAME & "';" & _
              "Initial Catalog='" & DB_NAME & "';" & _
              "Integrated Security=SSPI;" & _
              "DataTypeCompatibility=80;"
              
    '===============================
    'SQL Serverへ接続(SQL Server認証)
    '===============================
'    conStr = "Provider=" & PROVIDER & ";" & _
'              "Data Source=" & SERVER_NAME & ";" & _
'              "Initial Catalog=" & DB_NAME & ";" & _
'              "User ID=" & USER_NAME & ";" & _
'              "Password=" & PASSWORD & ";" & _
'              "DataTypeCompatibility=80;"
              
    'オープン
    Set con = CreateObject("ADODB.Connection")
    con.Open conStr
    
    On Error GoTo execStoproError
    
    Set command = CreateObject("ADODB.Command")
    
    '===============================
    'ストプロのパラメーターを設定
    '===============================
    Set commandParameter = command.CreateParameter()
    
    With commandParameter
        '引数名
        .Name = "message"
        '型を指定
        '┗VARCHAR：200
        '┗DECIMAL：14
        '┗DOUBLE ：5
        .Type = 200
        '桁数
        .Size = 50
        'パラメータの方向
        '┗入力：1
        '┗出力：2
        .Direction = 1
        '値
        .Value = "VBAからストプロ実行"
    End With
    
    command.Parameters.Append commandParameter
    
    '===============================
    'ストプロを実行
    '===============================
    With command
        .ActiveConnection = con
        'コマンドの種類を指定
        '┗SQL　　 ：1
        '┗ストプロ：4
        .CommandType = 4
        .CommandText = STORED_PROC
        .Execute
    End With

    MsgBox "ストプロを実行しました!"
    
    '===============================
    '後片付け
    '===============================
    con.Close
    Set commandParameter = Nothing
    Set command = Nothing
    Set con = Nothing
    
    Exit Sub

conError:
    '===============================
    '後片付け
    '===============================
    Set con = Nothing

    errorMessage = "接続でエラーが発生しました。" & vbCrLf & vbCrLf & _
            "●エラー番号" & vbCrLf & Err.Number & vbCrLf & vbCrLf & _
            "●エラー内容" & vbCrLf & Err.Description & vbCrLf
    MsgBox errorMessage, vbCritical
    Exit Sub
    
execStoproError:
    '===============================
    '後片付け
    '===============================
    con.Close
    Set commandParameter = Nothing
    Set command = Nothing
    Set con = Nothing

    errorMessage = "ストプロの実行でエラーが発生しました。" & vbCrLf & vbCrLf & _
            "●エラー番号" & vbCrLf & Err.Number & vbCrLf & vbCrLf & _
            "●エラー内容" & vbCrLf & Err.Description & vbCrLf
    MsgBox errorMessage, vbCritical
    Exit Sub

End Sub

結果

Excel(VBA)を使用して、SQL Serverのストプロ(引数あり)を実行できました。

参考①

上記のVBAコードで使用している以下の詳細については、公式サイトをご確認ください。

●CreateObject(“ADODB.Connection”)

●CreateObject(“ADODB.Command”)

●CreateParameterメソッド

参考②

Excel(VBA)を使用して、SQL ServerへSQLを実行することもできます。

詳細は以下の記事をご確認ください。