[XL]XLODBCアドイン関数(SQLServer上のストアドプロシージャの実行)

概要

本文書は、 Microsoft(R) Visual Basic(R) Programming System Applications
Edition (以下 VBA) で XLODBC アドイン関数を使用し、Excel 5.0 から
SQLServer 上のストアドプロシージャを実行する方法を、サンプルモジュールと
共に解説しています。

使用例

SQLServer 上のストアドプロシージャを実行するには、SQLExecQuery や SQLRequest で指定する SQL 文を次のように記述します。

  {call ストアドプロシージャ名}
  exec ストアドプロシージャ名

また、プロシージャが引数を持つ場合は次のように記述します。

  {call ストアドプロシージャ名 (param1,param2,....)}
  exec スロアドプロシージャ名 param1, param2

サンプル モジュールは「 SQL Server 」に対するものを用意しました。プログラ
ム実行環境は次の通りです。

  SQL Server
    プログラム実行環境
        サーバー          SQLServer
        ODBC ドライバ     SQLServerODBC ドライバ (Excel5.0 添付)
        データソース名    TstSQL
    TstSQL の内容
        サーバー名        serversql1
        ユーザー ID       sa
        データベース      pubs
        パスワード        Excel

XLODBC アドイン関数を使用したサンプル モジュールは次の通りです。
モジュールの記述で、次の記述で囲まれている部分は、紙面上では改行されていま
すが、実際のモジュール シートへの入力の際は改行してはいけないコードを
示しています。ご注意ください。

    '以下の 2 行は改行せずにコードを入力してください。
    '上の 2 行は改行せずにコードを入力してください。

SQLServer 上のストアドプロシージャを実行する

サンプル 1

次のサンプル モジュールでは、SQLServer 上のストアドプロシージャ
tst01proc を実行し、結果を SQLRetrieve で指定したセルに貼り付けています。

1-1

  Sub RunSQLProcedure1_1()
  Dim con As Variant
      'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
      '以下の 2 行は改行せずにコードを入力してください。
      con = SQLOpen("DSN=TstSQL;SERVER=serversql1;UID=sa;DATABASE=pubs;
      PWD=Excel", , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'ストアドプロシージャの実行
      SQLExecQuery con, "{call tst01proc}"
      '以前に実行されたクエリーの結果を取得し Sheet1 のセル A1 に貼り付け
      SQLRetrieve con, Range("Sheet1!A1"), , , True
      'データソースとの接続を切断
      SQLClose con
  End Sub

1-2

  Sub RunSQLProcedure1_2()
  Dim con As Variant
      'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
      '以下の 2 行は改行せずにコードを入力してください。
      con = SQLOpen("DSN=TstSQL;SERVER=serversql1;UID=sa;DATABASE=pubs;
      PWD=Excel", , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'ストアドプロシージャの実行
      SQLExecQuery connection, "exec tst01proc"
      '以前に実行されたクエリーの結果を取得し Sheet1 のセル A1 に貼り付け
      SQLRetrieve con, Range("Sheet1!A1"), , , True
      'データソースとの接続を切断
      SQLClose con
  End Sub

サンプル 2

SQLServer 上のストアドプロシージャ tst02proc に引数を渡し実行します。この
サンプルで使用するストアドプロシージャ tst02proc は書店番号を引数として
受け取り、その書店番号に該当するデータを抽出します。書籍番号は InputBox
を使用しユーザーに入力を施すようにしています。
ストアドプロシージャの実行と抽出されたデータの取得 は SQLRequest を使用
しています。

  Sub RunSQLProcedure2()
  Dim stored_proc, con, userno As String
  Dim Results
  Dim x As Integer
  Dim wksheet As Worksheet
  '書店番号の入力
  Lin1:
  userno = Application.InputBox(prompt:="抽出する書店番号を入力して下さい", _
      Title:="売上抽出", Type:=2)
      Select Case userno
          Case False
              'キャンセルボタンが押されたら処理終了
              MsgBox "処理を終了します"
              Exit Sub
          Case ""
              '何も入力されずに OK ボタンが押されたらもう一度入力するか
              'どうか確認
              Response = MsgBox(prompt:="書店番号が入力されていません" _
                  & Chr(10) & "もう一度入力しますか", _
                Buttons:=vbYesNo)
                'はい が押されたら書店番号の入力のダイアログを表示
                If Response = vbYes Then
                    GoTo Lin1
                Else
                'いいえ が押されたら処理を終了
                    MsgBox "処理を終了します"
                    Exit Sub
                End If
        End Select
  'プロシージャ呼び出し文定義
  stored_proc = "{Call tst02proc('" & userno & "')}"
  '接続文字列定義
  con = "DSN=TstSQL;SERVER=serversql1;UID=sa;DATABASE=pubs;PWD=Excel"
  'ストアドプシージャ実行
  '結果の配列を Results にセット
  Results = SQLRequest(con, stored_proc, , 2, True)
  'ワークシート Sheet1 に結果の貼り付け
  Set wksheet = ThisWorkbook.Worksheets("Sheet1")
  wksheet.Range(wksheet.Cells(1, 1), _
      wksheet.Cells(UBound(Results, 1), _
      UBound(Results, 2))).FormulaArray = Results
  SQLClose con
  End Sub

サンプル 3

SQLServer 上のストアドプロシージャ tst02proc に引数を渡し実行します。この
サンプルで使用するストアドプロシージャ tst02proc は書店番号を引数として
受け取りその書店番号に該当するデータを抽出します。書籍番号は InputBox を
使用しユーザーに入力を施すようにしています。
ストアドプロシージャの実行は SQLExecQuery を使用し、抽出されたデータの
貼り付けは SQLRetrieve を使用しています。

  Sub RunSQLProcedure3()
  Dim stored_proc, userno As String
  Dim x As Integer
  Dim con As Variant
  '書店番号の入力
  Lin1:
  userno = Application.InputBox(prompt:="抽出する書店番号を入力して下さい", _
      Title:="売上抽出", Type:=2)
      Select Case userno
          Case False
              'キャンセルボタンが押されたら処理終了
              MsgBox "処理を終了します"
              Exit Sub
          Case ""
              '何も入力されずに OK ボタンが押されたらもう一度入力するか
              'どうか確認
              Response = MsgBox(prompt:="書店番号が入力されていません" _
                  & Chr(10) & "もう一度入力しますか", _
                  Buttons:=vbYesNo)
                  'はい が押されたら書店番号の入力のダイアログを表示
                  If Response = vbYes Then
                      GoTo Lin1
                  Else
                  'いいえ が押されたら処理を終了
                      MsgBox "処理を終了します"
                      Exit Sub
                  End If
          End Select
  'プロシージャ呼び出し文定義
  stored_proc = "{Call tst02proc('" & userno & "')}"
  'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
  '以下の 2 行は改行せずにコードを入力してください。
  con = SQLOpen("DSN=TstSQL;SERVER=serversql1;UID=sa;DATABASE=pubs;
  PWD=Excel", , 2)
  '上の 2 行は改行せずにコードを入力してください。
  'ストアドプシージャ実行
  SQLExecQuery con, stored_proc
  '以前に実行されたクエリーの結果を取得し Sheet1 の A1 のセルに貼り付け
  SQLRetrieve con, Range("Sheet2!A1"), , , True
  'データソースとの接続を切断
  SQLClose con
  End Sub

サンプル 4

次のサンプル モジュールでは、2 つの引数を受け取り、リターン値を返すスト
アドプロシージャ TstPara を呼び出しています。

4-1

  'プロシージャー TstPara を呼び出す TstPara2 を作成し TstPara2 を実行する方法
  Sub RunSQLProcedure4_1()
  Dim con As Variant
  'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
  '以下の 2 行は改行せずにコードを入力してください。
  con = SQLOpen("DSN=TstSQL;SERVER=serversql1;UID=sa;DATABASE=pubs;
  PWD=Excel", , 2)
  '上の 2 行は改行せずにコードを入力してください。
      'ストアドプロシージャの実行
      SQLExecQuery con, "{call TstPara2}"
      '以前に実行されたクエリーの結果を取得し Sheet1 の A1 のセルに貼り付け
      SQLRetrieve con, Range("Sheet1!A1")
      'データソースとの接続を切断
      SQLClose con
  End Sub

4-2

  'プロシージャー TstPara を呼び出す TstPara2 を作成し TstPara2 を実行する方法
  Sub RunSQLProcedure4_2()
  Dim con As Variant
  'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
  '以下の 2 行は改行せずにコードを入力してください。
  con = SQLOpen("DSN=TstSQL;SERVER=serversql1;UID=sa;DATABASE=pubs;
  PWD=Excel", , 2)
  '上の 2 行は改行せずにコードを入力してください。
  'ストアドプロシージャの実行
  SQLExecQuery con, "exec TstPara2"
  '以前に実行されたクエリーの結果を取得し Sheet1 の A1 のセルに貼り付け
  SQLRetrieve con, Range("Sheet1!A1")
  'データソースとの接続を切断
  SQLClose con
  End Sub

サンプル 4 で使用した SQLServer ストアドプロシージャ TstPara2 (例)

  CREATE PROCEDURE TstPara2
  as
  declare @bb int
  exec TstPara 5, 6, @bb output
  select @bb

4-3

  'TstPara を declare から直接実行する方法
  Sub RunSQLProcedure4_3
  Dim con As Variant
  'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
  '以下の 2 行は改行せずにコードを入力してください。
  con = SQLOpen("DSN=TstSQL;SERVER=serversql1;UID=sa;DATABASE=pubs;
  PWD=Excel", , 2)
  '上の 2 行は改行せずにコードを入力してください。
  'ストアドプロシージャの実行
  '以下の 2 行は改行せずにコードを入力してください。
  SQLExecQuery con, "declare @bb int exec TstPara 5, 6,
  @bb output select @bb"
  '上の 2 行は改行せずにコードを入力してください。
  '以前に実行されたクエリーの結果を取得し Sheet1 の A1 のセルに貼り付け
  SQLRetrieve con, Range("Sheet1!A1")
  'データソースとの接続を切断
  SQLClose con
  End Sub