ストアドプロシージャ作り方（SQL Server）

ストアドプロシージャとは

DBを使用する際にDB側に設置できる関数のようなもの
呼出し側のツールでバリデーションやその他ビジネスルールを守るための処理などを書く必要がなくなり
複数のツールで利用する場合でも、言語関係なく同じ機能を呼出すことが可能になる
処理漏れやデータの不整合を防いだり、各プログラムで何度も同じ処理を書かなくて済む

ストアドプロシージャの作成例

下記のような商品マスタと、店舗が扱っている商品を登録している店舗商品マスタがあった場合
商品をマスタから削除する際に、店舗で登録されている商品かをチェックし削除するストアドプロシージャを作成

■ 商品マスタ

商品コード	商品名	単価
001	りんご	150
002	バナナ	100
003	みかん	250

■ 店舗商品マスタ

店舗コード	商品コード
A01	001
A01	002
A02	001

ストアドプロシージャの作成

CREATE PROCEDURE ProductDelete
    @商品コード varchar(20),
    @ErrorMessage   NVARCHAR(100) OUT
AS
BEGIN
	-- 商品コードが店舗商品マスタにあった場合　エラーメッセージを返す
    IF EXISTS
        (SELECT *
           FROM 店舗商品マスタ
         　WHERE 商品コード = @商品コード)
		BEGIN
			SET @ErrorMessage = N'商品を使用している店舗があるので削除できません';
			Return;
		END
	ELSE
	-- 該当しなかった場合は削除を実行
		BEGIN
			DELETE
			  FROM 商品マスタ
			 WHERE 商品コード = @商品コード;
			   SET @Message = N'削除完了';
			   Return;
		END
END;

VBAから呼び出し方（例）

　商品コード「001」を引数に渡し、削除するVBAコードを実行

' DBコネクションを作成
Dim CN As ADODB.Connection
Dim constr As String

Dim MYPROVIDERE = "Provider=SQLOLEDB;"
Dim MYSERVER = "Data Source=localhost;"   ' サーバー
Dim MYDATABASE = "Initial Catalog=Test;"  ' 接続するデータベース名
Dim USER = "User ID=sa;"                  ' SQL Server認証の場合のみ指定
Dim PSWD = "Password=12345;"              ' SQL Server認証の場合のみ指定

' DB接続
Set CN = New ADODB.Connection
CN.CursorLocation = 3 ' クライアントサイドカーソルに変更        
' SQL Server認証
constr = MYPROVIDERE & MYSERVER & MYDATABASE & USER & PSWD
CN.ConnectionString = constr
CN.Open
    
' コマンドの設定
Private CM As ADODB.Command
Set CM = New ADODB.Command
Set CM.ActiveConnection = CN

CM.CommandType = adCmdStoredProc ' ストアドプロシージャ用コマンド
CM.CommandText = "ProductDelete" ' プロシージャ名

' 引数がある場合は引数・戻り値がある場合は戻り値を受けるパラメータ
Dim objParam As ADODB.Parameter

' ひとつ目のパラメータ
Set objParam = CM.CreateParameter()
objParam.Name = "商品コード"       ' 引数の名前
objParam.Type = adChar            ' データ型（文字列）
objParam.Direction = adParamInput ' 引数を指定
objParam.Value = "001"         　 ' 引数の値

CM.Parameters.Append objParam ' パラメータ追加

' 戻り値のパラメータ
Set objParam = CM.CreateParameter()
objParam.Name = "Message"          ' 戻り値の名前（任意）
objParam.Type = adChar             ' データ型（文字列）
objParam.Direction = adParamOutput ' 戻り値を指定

CM.Parameters.Append objParam ' パラメータ追加
CM.Execute                    ' コマンド実行

Dim returnValue As String
returnValue = CM.Parameters("Message").Value　' 戻り値の文字列を代入
Debug.Print returnValue　　　　　　　　　　　   ' デバッグプリント

' DB接続解除
Set objParam = Nothing
Set CM = Nothing
Set CN = Nothing

**結果：**エラーメッセージを取得「商品を使用している店舗があるので削除できません」

上記プロシージャを使用することで、呼出し側で（今回はVBAだが言語関係なく）
店舗で商品が使用されているかを検索するプログラムを書かなくても、削除するプログラムのみ書くだけで済む