みどりのジーパン

Data Science / Data Engineering / MLOpsを勉強中

【T-SQL】カスタムエラーで誤ったProcedure実行を止めたい

業務で必要になって調査したら意外と使えそうだったのでメモ。

Procedure内でカスタムエラーを実装して、問題ない場合のみ実行させたくなりました。 ここで言う「問題ない場合」とは以下の2つがあると思います。

  • パラメータにTypoがない
  • 使用するテーブルに不具合がない(データ連携が遅れていない、欠損がない)

この2つの項目を満たせないままProcedureを実行すると、思わぬ事故につながる可能性があります。


例として、カスタムエラーでProcedure実行を止められないとどうなるか考えてみましょう。

deleteinsertを行う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は簡単に実装できると思います。

  • trycatchに分けて、うまくいった時、エラー出た時の2つに分けて記述する。
  • エラー出た時に元に戻せるように、transactionを組み込む。
    • deleteのみ or insertのみ行われるという事故を防ぐことができる。
  • エラーの出力は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

SQLserverにおけるエラーハンドリングの実装方法(翻訳) - Qiita