【T-SQL】カスタムエラーで誤ったProcedure実行を止めたい
業務で必要になって調査したら意外と使えそうだったのでメモ。
Procedure内でカスタムエラーを実装して、問題ない場合のみ実行させたくなりました。 ここで言う「問題ない場合」とは以下の2つがあると思います。
- パラメータにTypoがない
- 使用するテーブルに不具合がない(データ連携が遅れていない、欠損がない)
この2つの項目を満たせないままProcedureを実行すると、思わぬ事故につながる可能性があります。
例として、カスタムエラーでProcedure実行を止められないとどうなるか考えてみましょう。
delete
とinsert
を行うProcedureを仮定します。
もしdelete
してしまった後に、insert
で失敗していたとします。
すると、delete
は実行されているにも関わらず、insert
は実行されていない、いわゆるデータ不整合が発生する可能性があります。
特にこの不具合をユーザーが把握できていないと最悪で、知らぬ間にその後の分析に悪影響を及ぼします。
思わぬデータ不整合を生まないために、独自に設定したバリデーションなどをカスタムエラーとして実装し、Procedureの実行可否を自動判断させる必要があります。
以下のような構造でSQLを書けば、とりあえずTypoやデータの不具合を検知して、問題なければProcedure実行ということができそうです。
ここでは適当なパラメータやテーブルを元に、tbl
のとあるレコードをtbl_bk
内の別のレコードに洗い替える処理を仮定します。
alter procedure procedure_hoge ( @param1 nvarchar(128) @param2 nvarchar(128) ) begin -- 何事もなければtryで完結する begin try begin transaction; -- 表記揺れやデータ不具合を条件分岐で検知 if (select count(1) from tbl where colA = @param1) = 0 begin -- ここからcatch内に飛ぶ, 内容はエラーとして出力される raiserror('@param1 does not exist in tbl', 16, 1); end delete from tbl where colA = @param1 if (select count(1) from tbl where colA = @param2) > 0 begin raiserror('@param2 already exist in tbl', 16, 2); end else if (select count(1) from tbl_bk where colA = @param2) = 0 begin raiserror('You did a typo at @param2.', 16, 3); end insert into tbl from tbl_bk where tbl_bk.colA = @param2 commit transaction; end try -- error検知したら、こちらに移る。以下コピペでOK。 begin catch -- try内transactionを無かったことにする rollback transaction; declare @errorMessage nvarchar(4000); declare @errorSeverity int; declare @errorState int; select @errorMessage = error_message() , @errorSeverity = error_severity() , @errorState = error_state() ; raiserror (@errorMessage, @errorSeverity, @errorState); end catch end ;
基本的には以下項目を守れば、上記のようなSQLは簡単に実装できると思います。
try
とcatch
に分けて、うまくいった時、エラー出た時の2つに分けて記述する。- エラー出た時に元に戻せるように、
transaction
を組み込む。delete
のみ orinsert
のみ行われるという事故を防ぐことができる。
- エラーの出力は
raiserror
を使う。
raiserror
についてもう少し触れます。
「eが1つ足りなくないか?」と思ったあなた。私も思いましたが、どうやら不足している状態が正しいみたいです。気持ち悪いですね。
それはさておき、raiserror
を使用した部分を再掲します。
raiserror('@param1 does not exist in tbl', 16, 1);
上記のクエリでは3つの項目があります。特別な事情がなければこの3つでよさそうです。
1つ目はメッセージです。この部分がエラーメッセージとして出力されることになります。
2つ目はエラーの重大度を表現するみたいです。こだわりがなければ16=「ユーザーが訂正できるエラー」でよさそうです。詳しくは ドキュメント読んでください。
データベース エンジン エラーの重大度 - SQL Server | Microsoft Docs
3つ目は状態を表現するみたいです。ここは正直どう活用すべきか分かっていません。0から255の整数でとりあえずクエリが動きはするので、それ以上深追いはしてません。
参考文献(本文掲載以外)
RAISERROR (Transact-SQL) - SQL Server | Microsoft Docs
トランザクション処理をさらっとマスターしよう:さらっと覚えるSQL&T-SQL入門(12)(3/3 ページ) - @IT