Server

【SqlServer】パラメーター付きVBAでストアドプロシージャを実行

 

前回は、AccessのVBAからSqlServerのストアドプロシージャを実行するというのをやりましたが、


【SqlServer】ストアドプロシージャを使って初めての処理

 

今回は、

 

ココがポイント

Accessの方でパラメーターを付けたVBAでストアドプロシージャを実行する

 

ということをやってみました。

簡単だと思っていましたが結局1日手こずりしました。

 

 

SSMSでSQLコードを作成

 

だんだんと、SQLにも慣れてきました・・・というより、SSMS内で「新しいクエリ」からの、「エディターでクエリをデザイン」モードを使えば簡単にSQLが作成できることがわかりました。

 

 

今回は、Accessの方からパラメーター付きでSqlServerに実行させたいので、ストアドプロシージャの中にもパラメーターを埋め込みます。

 

ココがポイント

@arrange_idというパラメーターでデータを絞る

 

ような事をさせてみます。

 

 

 

パラメーターを設定したVBA作成

Sub shipping_schedule_calc()
    Dim Cnn As New ADODB.Connection
    Dim cmd As New ADODB.Command
'    Dim param As ADODB.Parameter
'    Dim strMsg As String
    
On Error GoTo Err_shipping_schedule_calc
'        Debug.Print Forms![stock_inquiry]![arrange_id]
  
    'SQLServer接続設定&接続
    With Cnn
        .Provider = "SQLOLEDB"
        .ConnectionString = "Data Source=サーバー名;" & _
            "Initial Catalog=データベース名;" & _
            "User ID=lユーザー名;" & _
            "Password=パスワード"
        .Open
    End With
    

    'ストアドプロシージャ呼び出し設定&呼び出し
            With cmd
                .CommandTimeout = 0             'タイムアウト設定を無制限に
                .ActiveConnection = Cnn         'サーバー接続設定
                .CommandText = "shipping_schedule_calc_add"      'ストアド名セット
                .CommandType = adCmdStoredProc  'ストアドなのでadCmdStoredProc
                .Parameters("@arrange_id").Value = Forms![stock_inquiry]![arrange_id]
                
            
                '戻り値設定
'                .Parameters.Append .CreateParameter("@rtn", adInteger, adParamReturnValue)

                '実行※レコードは返ってこないのでadExecuteNoRecords
                .Execute , , adExecuteNoRecords

                '戻り値の判断&表示用メッセージ生成
'                If .Parameters.Item("@rtn").Value = 0 Then
'                    strMsg = "出荷予定数の計算は正常終了しました。"
'                Else
'                    strMsg = "異常終了しました" & vbCrLf & _
'                            "ErrNO=" & .Parameters.Item("@rtn").Value
'                End If
            End With
            
   
Exit_shipping_schedule_calc:
    '接続解除
        Cnn.Close: Set Cnn = Nothing
    'メッセージ表示
'        MsgBox strMsg
    Exit Sub

Err_shipping_schedule_calc:
'                DoCmd.Close acForm, "jobwait", acSaveNo
    'ストアド以外の場所でのエラーなので、VBAのErrの情報セット
'    strMsg = "異常終了しました" & vbCrLf & _
'            "ErrNO=" & Err.number & " ErrMsg=" & Err.description
'    Resume Exit_shipping_schedule_calc
            MsgBox "エラー: " & Err.description

End Sub

 

ちょっと上のコードは、コメントアウトが多すぎるので見ずらいとは思いますが、ポイントは、

 

.Parameters("@arrange_id").Value = Forms![stock_inquiry]![arrange_id]

の部分で、

 

フォームの値をパラメーターとして設定している

 

という事です。このようにして、フォームの値をストアドプロシージャに渡すことができれば、本当に便利ですよね。

 

 

 

VBAのエラー戻り値があるとエラー

'戻り値設定 ' .Parameters.Append .CreateParameter("@rtn", adInteger, adParamReturnValue)

 

コメントアウトしてある、ストアドプロシージャでエラーがあった時の戻り値の処理部分ですが、このコードを入れるとエラーになります。

私の現時点でのスキルでは何でエラーになるのかが理解できないので、コメントアウトして消す事にしました。

ストアド内でエラーが起きたら、ロールバックさせるかと思いますが、エラーがあったという事は通知されないのかもしれません。

時間があるときにもっと追及してみたいと思います。

 

 

ストアドプロシージャのコード

USE [Logistics]
GO
/****** Object:  StoredProcedure [dbo].[shipping_schedule_calc_add]    Script Date: 2020/03/14 14:05:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[shipping_schedule_calc_add]
@arrange_id VARCHAR(50)
AS
BEGIN TRY

    BEGIN TRANSACTION
 
UPDATE stock
SET waiting_shipping = waiting_shipping + View_shipping_schedule_calc.出荷予定数
FROM View_shipping_schedule_calc INNER JOIN stock
ON View_shipping_schedule_calc.product_sku = stock.jan_sku_code
WHERE View_shipping_schedule_calc.arrange_date = @arrange_id

UPDATE dbo.[order]
SET dbo.[order].progress = 2
WHERE dbo.[order].arrange_date = @arrange_id

 
    COMMIT TRANSACTION
 
END TRY
 
BEGIN CATCH
    ROLLBACK TRANSACTION
 
    RETURN ERROR_NUMBER()
 
END CATCH
 
RETURN 0

 

 

上のストアドプロシージャでの注意ポイントとしては、

@arrange_id VARCHAR(50)

 

といいうように、ちゃんとパラメーターの宣言をしてあげないとエラーがでます。

そして、SSMSで作成したSQLを組み込みますが、

 

WHERE View_shipping_schedule_calc.arrange_date = @arrange_id

 

の様に、「@arrange_id」のようにダイレクトに入れればいいらしく、とくにカンマやダブルコーテーションなども必要ないようです。

 

 

今まではストアドプロシージャを敬遠していましたが、ちょっと覚えればサーバー側で処理してくれるので端末側のAccessの負担が減って処理が楽になります。

 

 

 

 

-Server
-, , , , ,

Translate »

© 2020 PCTips