分かりやすく、使いやすく。

ストアドプロシージャに引数を渡して実行する方法

スポンサーリンク

Excel VBA から SQL Server のストアドプロシージャに引数を渡して実行するサンプルコードです。

  1. SQL Server側のストアドプロシージャ
  2. VBA のサンプルコード


SQL Server側のストアドプロシージャ

事前に SQL Server に以下のストアドプロシージャを作成しておきます。このストアドプロシージャは、渡された引数を TEST.dbo.TEST_TABLE テーブルに INSERT します。（ TEST.dbo.TEST_TABLE テーブルが存在しない場合は、テーブル作成も行います。）

sql（実行可能なサンプルコード）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE PROCEDURE TEST_PROC
(
    @TEXT1 AS VARCHAR(50)
  , @NUM1 AS DECIMAL(12,2)
)
AS
BEGIN
    IF OBJECT_ID('TEST.dbo.MY_TABLE', 'U') IS NULL
    BEGIN
        --TEST_TABLEが存在ない場合は、SELECT INTO で新規テーブルを作成します。
        SELECT @TEXT1 AS TEXT1, @NUM1 AS NUM1
        INTO TEST.dbo.MY_TABLE;
    END
    ELSE
    BEGIN
        --TEST_TABLEが存在する場合は、引数で与えられた値を登録します。
        INSERT INTO TEST.dbo.MY_TABLE
        VALUES(@TEXT1, @NUM1);
    END
END  
▲このページの一番上に戻る

VBA のサンプルコード

上記のストアドプロシージャに引数を渡して実行する Excel VBA のサンプルコードは次の通りです。

このコードを実行するには、事前にADODBへの参照設定が必要です。

（参考）ExcelでADODBの参照設定を行う方法

VBA（実行可能なサンプルコード）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

Option Explicit
 
Sub test()
    '■接続先情報を設定
    'この例では、ローカルのインスタンス「SQLEXPRESS」にWindows認証で接続する。
    Dim connectionString As String
    
    Dim dataSourceName As String
    Dim databaseName As String
    
    '「.」はローカルを表す。他のサーバに接続する場合はホスト名を指定。
    dataSourceName = ".\SQLEXPRESS" '
    
     'TESTデータベースに接続する。
    databaseName = "TEST"
    
    'ODBC接続の接続文字列を作成する。
    connectionString = ""
    connectionString = connectionString & "PROVIDER=MSDASQL;"
    connectionString = connectionString & "DRIVER={SQL Server};"
    connectionString = connectionString & "SERVER=" & dataSourceName & ";"
    connectionString = connectionString & "INITIAL CATALOG=" & databaseName & ";"
    connectionString = connectionString & "Trusted_connection=yes; "
    
    '■接続
    Dim objConnection As ADODB.Connection
    
    Set objConnection = New ADODB.Connection
    
    objConnection.CursorLocation = adUseClient
    objConnection.Open connectionString
    
    '■実行するストアドプロシージャを設定
    Dim objCommand As ADODB.Command
    
    Set objCommand = New ADODB.Command
    Set objCommand.ActiveConnection = objConnection
    
    objCommand.CommandType = adCmdStoredProc
    objCommand.CommandText = "TEST_PROC"
    
    '■ストアドプロシージャに渡す引数を設定
    Dim objParameter As ADODB.Parameter
    
    '１個目の引数：VARCHAR(50)
    Set objParameter = objCommand.CreateParameter()
    objParameter.Name = "TEXT1" '引数の名前
    objParameter.Type = adChar '文字列
    objParameter.Size = 50 '文字列のサイズ
    objParameter.Direction = adParamInput '入力パラメータ
    objParameter.Value = "テスト登録データ　" & Now() '渡す値
    
    objCommand.Parameters.Append objParameter
    
    '２個目の引数：Decimal(12,2)
    Set objParameter = objCommand.CreateParameter()
    objParameter.Name = "NUM1" '引数の名前
    objParameter.Type = adDecimal 'DECIMAL
    objParameter.NumericScale = 2 '小数点以下の桁数
    objParameter.Precision = 12 '精度
    objParameter.Direction = adParamInput '入力パラメータ
    objParameter.Value = 1234567890.12 '渡す値
    
    objCommand.Parameters.Append objParameter
    
    '■ストアドを実行
    objCommand.Execute
    
    '■終了処理
    Set objParameter = Nothing
    Set objCommand = Nothing
    Set objConnection = Nothing
End Sub

実行後に TEST.dbo.MY_TABLE の中身を参照すると、引数で渡した値が登録されています。

▲このページの一番上に戻る
スポンサーリンク
カテゴリー
スポンサーリンク