[翻訳] SQL Serverのエラーおよびトランザクション処理。 パート1.エラー処理-クイックスタート

こんにちは、Habr！ 「SQL Serverでのエラーとトランザクションの処理」という記事の翻訳を紹介します。 パート 1- ジャンプスタートエラー処理」 、Erland Sommarskog著。

1.はじめに

この記事は、SQL Serverのエラー処理とトランザクション処理に関する一連の3つの記事の最初の記事です。 その目標は、ほとんどのコードに適した基本的な例を示すことにより、エラー処理の主題についてクイックスタートを提供することです。 このパートは、経験の浅い読者向けに書かれたものです。そのため、多くの詳細については慎重に黙っています。 現時点でのタスクは、 理由を強調せずに方法を伝えることです。 あなたが信仰について私の言葉を信じるなら、あなたはこのパートを読んで、あなたのキャリアのさらなるステップのために他の2つを延期することができます。



一方、私の推奨事項に疑問がある場合は、他の2つの部分を読む必要があります。SQLServerのエラーとトランザクション処理の非常に紛らわしい世界を探りながら、詳細をさらに深く掘り下げます。 2番目と3番目の部分、および3つのアプリケーションは、より深い経験を持つ読者を対象としています。 最初の記事は短く、2番目と3番目ははるかに長いです。



すべての記事では、バージョン2005以降のSQL Serverでのエラーおよびトランザクション処理について説明しています。

1.1なぜエラー処理が必要なのですか？

コードのエラーを処理するのはなぜですか？ これには多くの理由があります。 たとえば、アプリケーションのフォームでは、入力されたデータを確認し、入力中に犯した間違いについてユーザーに通知します。 ユーザーエラーは予測可能なエラーです。 しかし、 予期しないエラーも処理する必要があります。 つまり、コードを書くときに何かを見落としたという事実が原因でエラーが発生する可能性があります。 簡単なアプローチは、実行を中断するか、少なくとも何が起きているかを完全に制御できる段階に戻ることです。 予期しないエラーを無視することは完全に容認できないことを単に強調するだけでは十分ではありません。 これは悲惨な結果を引き起こす可能性がある欠陥です。たとえば、アプリケーションがユーザーに誤った情報を提供したり、さらに悪いことに、データベースに誤ったデータを保存したりする可能性があります。 また、ユーザーが操作が成功したとは思わないようにエラーを報告することも重要ですが、コードは実際には何もしませんでした。



多くの場合、データベースの変更はアトミックにしたいです。 たとえば、あるアカウントから別のアカウントに送金するタスク。 このため、CashHoldingsテーブルの2つのエントリを変更し、Transactionsテーブルに2つのエントリを追加する必要があります。 エラーや失敗がお金が受取人の口座に送金されるという事実に至り、それらが差出人の口座から引き落とされることは絶対に許されません。 このため、エラー処理はトランザクション処理にも適用されます。 上記の例では、BEGIN TRANSACTIONおよびCOMMIT TRANSACTIONで操作をラップする必要がありますが、それだけではありません。エラーが発生した場合、トランザクションがロールバックされることを確認する必要があります。

2.基本チーム

まず、エラーを処理するために必要な最も重要なコマンドを確認します。 2番目の部分では、エラーおよびトランザクション処理に関連するすべてのコマンドについて説明します。

2.1トライキャッチ

主なエラー処理メカニズムはTRY-CATCHコンストラクトであり、他の言語の同様のコンストラクトを非常に連想させます。 構造は次のとおりです。

BEGIN TRY < > END TRY BEGIN CATCH < > END CATCH
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

< >



にエラーが表示された場合、実行はCATCHブロックに転送され、エラー処理コードが実行されます。



通常、CATCHは開いているトランザクションをロールバックし、エラーを再生成します。 したがって、呼び出し元のクライアントは、何かがうまくいかなかったことを理解します。 エラーのリコールについては、この記事の後半で説明します。



非常に簡単な例を次に示します。

 BEGIN TRY DECLARE @x int SELECT @x = 1/0 PRINT 'Not reached' END TRY BEGIN CATCH PRINT 'This is the error: ' + error_message() END CATCH
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

実行結果： This is the error: Divide by zero error encountered.







後でerror_message（）関数に戻ります。 CATCHハンドラーでのPRINTの使用は、実験の一部としてのみ提供されており、実際のアプリケーションコードでは実行しないでください。



< >



がストアドプロシージャを呼び出すか、トリガーをトリガーする場合、それらで発生するエラーは実行をCATCHブロックに渡します。 具体的には、エラーが発生すると、SQL ServerはCATCHハンドラーが見つかるまでスタックをスピンします。 そのようなハンドラーがない場合、SQL Serverはエラーメッセージをクライアントに直接送信します。



TRY-CATCH構造には、知っておく必要のある非常に重要な制限が1つあります。同じスコープで発生するコンパイルエラーをキャッチしません。 例を考えてみましょう：

 CREATE PROCEDURE inner_sp AS BEGIN TRY PRINT 'This prints' SELECT * FROM NoSuchTable PRINT 'This does not print' END TRY BEGIN CATCH PRINT 'And nor does this print' END CATCH go EXEC inner_sp
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

出力データ：

 This prints Msg 208, Level 16, State 1, Procedure inner_sp, Line 4 Invalid object name 'NoSuchTable'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ご覧のとおり、TRYブロックは存在しますが、エラーが発生すると、期待どおりに実行がCATCHブロックに渡されません。 これは、実行時に発生する列の欠落、無効なエイリアスなどのすべてのコンパイルエラーに適用されます。 （遅延名前解決-存在しないテーブルにアクセスするプロシージャをSQL Serverが作成できるようにする機能により、実行時にSQL Serverでコンパイルエラーが発生する可能性があります。）



これらのエラーは完全にとらえどころのないものではありません。 それらが発生するエリアでは捕まえられませんが、外部エリアでは捕まえられます。 このコードを前の例に追加します。

 CREATE PROCEDURE outer_sp AS BEGIN TRY EXEC inner_sp END TRY BEGIN CATCH PRINT 'The error message is: ' + error_message() END CATCH go EXEC outer_sp
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次の出力が得られます。

 This prints The error message is: Invalid object name 'NoSuchTable'.
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

今回は、外部CATCHハンドラーが機能したため、エラーがキャッチされました。

2.2 SET XACT_ABORT ON

常に、この式をストアドプロシージャの先頭に追加します。

 SET XACT_ABORT, NOCOUNT ON
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

以前のバージョンとの互換性のためにデフォルトでオフになっている2つのセッションパラメーターをアクティブにしますが、経験上、これらのパラメーターを常にオンにすることが最善のアプローチであることが証明されています。 TRY-CATCHを使用しない場合のSQL Serverのデフォルトの動作では、一部のエラーは実行を中断し、開いているトランザクションをロールバックしますが、他のエラーでは後続の命令の実行は継続します。 XACT_ABORT ONを有効にすると、ほとんどすべてのエラーが同じ効果を引き起こし始めます。開いているトランザクションはすべてロールバックされ、コードの実行は中断されます。 いくつかの例外がありますが、その中で最も注目すべきは式RAISERRORです。



XACT_ABORTパラメーターは、より信頼性の高いエラーおよびトランザクション処理のために必要です。 特に、デフォルト設定では、TRY-CATCHを使用している場合でも、トランザクションのロールバックなしで実行を中止できる状況がいくつかあります。 前のセクションでそのような例をみましたが、TRY-CATCHは同じ領域で発生したコンパイルエラーをキャッチしないことがわかりました。 エラーのためにロールバックされなかったオープントランザクションは、アプリケーションがトランザクションを完了またはロールバックせずに実行を継続する場合、重大な問題を引き起こす可能性があります。



SQL Serverで信頼性の高いエラー処理を行うには、TRY-CATCHとSET XACT_ABORT ONの両方が必要です。 中でも、SET XACT_ABORT ONステートメントが最も重要です。 産業環境でコードだけに頼るべきではない場合、迅速で簡単なソリューションに非常に適しています。



NOCOUNTパラメーターはエラー処理とは関係ありませんが、コードに含めることをお勧めします。 NOCOUNTは、SQL Server Management Studioの[メッセージ]パネルに表示される形式（影響を受ける1行）のメッセージを抑制します。 これらのメッセージはSSMSを使用する場合に役立ちますが、ネットワークトラフィックが増加するため、アプリケーションのパフォーマンスに悪影響を与える可能性があります。 行数に関するメッセージは、記述が不十分なクライアントアプリケーションでもエラーを引き起こす可能性があり、これはリクエストが返したデータであると考える場合があります。



上記では、少し珍しい構文を使用しました。 ほとんどの人は、2つの別個の式を記述します。

 SET NOCOUNT ON SET XACT_ABORT ON
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

それらの間に違いはありません。 私はSETとカンマ付きのバージョンが好きです、なぜなら これにより、コードノイズが減少します。 これらの式はすべてのストアドプロシージャに表示されるため、使用するスペースはできる限り少なくする必要があります。

3.エラー処理の主な例

TRY-CATCHとSET XACT_ABORT ONを確認した後、すべてのストアドプロシージャで使用できる例にまとめましょう。 最初に、エラーが単純な形式で生成される例を示します。次のセクションでは、より良い解決策について説明します。



例として、この単純なテーブルを使用します。

 CREATE TABLE sometable(a int NOT NULL, b int NOT NULL, CONSTRAINT pk_sometable PRIMARY KEY(a, b))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

エラーとトランザクションの処理方法を示すストアドプロシージャを次に示します。

 CREATE PROCEDURE insert_data @a int, @b int AS SET XACT_ABORT, NOCOUNT ON BEGIN TRY BEGIN TRANSACTION INSERT sometable(a, b) VALUES (@a, @b) INSERT sometable(a, b) VALUES (@b, @a) COMMIT TRANSACTION END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION DECLARE @msg nvarchar(2048) = error_message() RAISERROR (@msg, 16, 1) RETURN 55555 END CATCH
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

上記で示したように、手順の最初の行には、1つの式にXACT_ABORTとNOCOUNTが含まれています。 この行は、BEGIN TRYの前の唯一の行です。 プロシージャ内の他のすべては、BEGIN TRYの後に配置する必要があります。変数の宣言、一時テーブルの作成、テーブル変数、それだけです。 手順に他のSETコマンドがある場合でも（この理由はまれですが）、BEGIN TRYの後に来る必要があります。



BEGIN TRYの前にSET XACT_ABORTとNOCOUNTを指定することを好む理由は、これを1行のノイズとして見ているためです。常に存在するはずですが、見た目を乱したくないのです。 もちろん、これは好みの問題であり、BEGIN TRYの後にSETコマンドを配置することを好む場合、それは大丈夫です。 重要なことは、BEGIN TRYの前に他のものを置かないことです。



BEGIN TRYとEND TRYの間の部分が手順の主要部分です。 ユーザー定義のトランザクションを使用したかったため、ペアを挿入した場合は逆のペアも挿入する必要があるという、かなり先取りされたビジネスルールを導入しました。 2つのINSERTステートメントは、BEGINおよびCOMMIT TRANSACTION内にあります。 多くの場合、BEGIN TRYとBEGIN TRANSACTIONの間に多くのコード行があります。 また、COMMIT TRANSACTIONとEND TRYの間にコードがある場合もありますが、これは通常、最終的なSELECTであり、データを返すか、出力パラメーターに値を割り当てます。 プロシージャが変更を加えない場合、またはINSERT / UPDATE / DELETE / MERGE式が1つだけの場合、通常、トランザクションを明示的に指定する必要はありません。



TRYブロックは手順ごとに異なるように見えますが、CATCHブロックはコピーアンドペーストの結果が多少異なります。 つまり、短く簡単なことをしてから、考えすぎずにどこでも使用します。 上記のCATCHハンドラーは3つのことを行います。

1. 開いているトランザクションをロールバックします。
2. エラーを再引き起こします。
3. プロシージャによって返される値がゼロ以外であることを確認します。

これらの3つのアクションは常に存在する必要があります。 私たちはそのラインに反対することができます

 IF @@trancount > 0 ROLLBACK TRANSACTION
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

プロシージャに明示的なトランザクションがない場合は不要ですが、これは絶対に間違っています。 おそらく、トランザクションを開くストアドプロシージャを呼び出していますが、TRY-CATCHの制限のためにトランザクションをロールバックできません。 おそらく、あなたや他の誰かが2年以内に明示的なトランザクションを追加するでしょう。 その場合、ロールバック付きの行を追加する必要があることを覚えていますか？ それに頼らないでください。 また、プロシージャを呼び出す人がトランザクションを開いた場合、トランザクションをロールバックしてはならないことに反対する読者の声も聞きます。いいえ、そうすべきです。理由を知りたい場合は、2番目と3番目の部分を読む必要があります。 CATCHハンドラーでのトランザクションのロールバックは、例外のない絶対的な命令です。



エラー再生成コードには次の行が含まれます。

 DECLARE @msg nvarchar(2048) = error_message()
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

組み込みのerror_message（）関数は、発生したエラーのテキストを返します。 次の行では、RAISERROR式を使用してエラーが呼び出されます。 これはエラーを発生させる最も簡単な方法ではありませんが、機能します。 他の方法については、次の章で説明します。



注 ：DECLAREで変数を初期化する構文は、SQL Server 2008で導入されました。SQLServer 2005を使用している場合は、文字列をDECLAREとSELECTステートメントに分割する必要があります。



RETURNの最後の表現は保険です。 RAISERRORは中止されることはないため、次のステートメントは続行されます。 すべての手順がTRY-CATCHを使用し、すべてのクライアントコードが例外を処理しますが、心配する必要はありません。 ただし、SQL Server 2005以前およびTRY-CATCH実装前に記述された古いコードからプロシージャを呼び出すことができます。 当時、できることは戻り値を見ることでした。 RETURNで返される値は、null値でない場合は実際には問題になりません（通常、ゼロは正常なシャットダウンを示します）。



プロシージャの最後の式はEND CATCHです。 END CATCHの後にコードを配置しないでください。 手順を読んでいる人には、このコードが表示されない場合があります。



理論を読んだ後、テストケースを試してみましょう：

 EXEC insert_data 9, NULL
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

実行結果：

 Msg 50000, Level 16, State 1, Procedure insert_data, Line 12 Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

外部プロシージャを追加して、エラーが再度呼び出されたときに何が起こるかを見てみましょう。

 CREATE PROCEDURE outer_sp @a int, @b int AS SET XACT_ABORT, NOCOUNT ON BEGIN TRY EXEC insert_data @a, @b END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION DECLARE @msg nvarchar(2048) = error_message() RAISERROR (@msg, 16, 1) RETURN 55555 END CATCH go EXEC outer_sp 8, 8
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

仕事の結果：

 Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9 Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

正しいエラーメッセージを受け取りましたが、このメッセージのヘッダーと前のヘッダーを見ると、問題に気付く場合があります。

 Msg 50000, Level 16, State 1, Procedure insert_data, Line 12 Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

エラーメッセージには、最終的なRAISERROR式の場所が表示されます。 最初のケースでは、行番号のみが間違っています。 2番目のケースでは、プロシージャ名も正しくありません。 テストケースのような簡単な手順では、これは大きな問題ではありません。 ただし、ネストされた複雑なプロシージャのレベルが複数ある場合、エラーの発生場所が示されていないエラーメッセージがあると、エラーの検出と修正がはるかに困難になります。 このため、エラーコードフラグメントの場所をすばやく特定できるようにエラーを生成することをお勧めします。これは次の章で検討することです。

4.エラーを生成する3つの方法

4.1 error_handler_spの使用

エラーメッセージのテキストを返すerror_message（）関数を見ました。 エラーメッセージはいくつかのコンポーネントで構成され、それぞれに独自の関数error_xxx（）があります。 それらを使用して、元の情報を含む完全なメッセージを再生成できますが、形式は異なります。 すべてのCATCHハンドラでこれを行うと、コードの重複という大きな欠点になります。 error_message（）および他の同様の関数を呼び出すためにCATCHブロックにいる必要はなく、CATCHブロックが実行するストアドプロシージャから呼び出された場合、同じ情報を返します。



error_handler_spを紹介します。

 CREATE PROCEDURE error_handler_sp AS DECLARE @errmsg nvarchar(2048), @severity tinyint, @state tinyint, @errno int, @proc sysname, @lineno int SELECT @errmsg = error_message(), @severity = error_severity(), @state = error_state(), @errno = error_number(), @proc = error_procedure(), @lineno = error_line() IF @errmsg NOT LIKE '***%' BEGIN SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + ', Line ' + ltrim(str(@lineno)) + '. Errno ' + ltrim(str(@errno)) + ': ' + @errmsg END RAISERROR('%s', @severity, @state, @errmsg)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

error_handler_spの最初の機能は、すべてのerror_xxx（）関数の値をローカル変数に保存することです。 すぐに式IFに戻ります。 代わりに、IF内のSELECTステートメントを見てみましょう。

 SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + ', Line ' + ltrim(str(@lineno)) + '. Errno ' + ltrim(str(@errno)) + ': ' + @errmsg
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このSELECTの目的は、RAISERRORに渡されるエラーメッセージをフォーマットすることです。 これには、元のエラーメッセージからのすべての情報が含まれており、RAISERRORに直接挿入することはできません。 プロシージャ名を処理する必要がありますが、通常のスクリプトまたは動的SQLのエラーの場合はNULLになる場合があります。 したがって、COALESCE関数が使用されます。 （式RAISERRORの形式がわからない場合は、第2部で詳しく説明します。）



フォーマットされたエラーメッセージは、3つのアスタリスクで始まります。 これにより、2つの目標が達成されます。1）このメッセージがCATCHハンドラーから呼び出されることがすぐにわかります。 2）これにより、error_handler_spがNOT LIKE '***％'条件を使用して既に1回以上生成されたエラーをフィルターで除外し、メッセージの2回目の変更を回避できます。



これは、error_handler_spを使用するときにCATCHハンドラーがどのように見えるかです。

 BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION EXEC error_handler_sp RETURN 55555 END CATCH
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

いくつかのテストケースを試してみましょう。

 EXEC insert_data 8, NULL EXEC outer_sp 8, 8
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

実行結果：

 Msg 50000, Level 16, State 2, Procedure error_handler_sp, Line 20 *** [insert_data], Line 5. Errno 515: Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails. Msg 50000, Level 14, State 1, Procedure error_handler_sp, Line 20 *** [insert_data], Line 6. Errno 2627: Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

メッセージヘッダーは、error_handler_spプロシージャでエラーが発生したことを示していますが、エラーメッセージのテキストから、エラーの実際の場所（プロシージャの名前と行番号の両方）がわかります。



エラーを呼び出すもう2つの方法を紹介します。 ただし、error_handler_spは、この部分を読んでいる読者に対する私の主な推奨事項です。 これは、2005年以降、SQL Serverのすべてのバージョンで実行されているシンプルなオプションです。欠点は1つだけです。場合によっては、SQL Serverが2つのエラーメッセージを生成しますが、error_xxx（）関数はそのうちの1つのみを返すため、メッセージの1つが失われます。 これは、BACKUP \ RESTOREのような管理コマンドを操作する場合は不便かもしれませんが、アプリケーション専用に設計されたコードでは問題がほとんど発生しません。

4.2。 使用法、スロー

SQL Server 2012で、Microsoftは式を導入しました;エラー処理を容易にするためのTHROW。 残念ながら、Microsoftはこのコマンドの設計に重大な間違いを犯し、危険なtrapを作成しました。



式; THROWでは、ストアドプロシージャは必要ありません。 CATCHハンドラーは次のように簡単になります。

 BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION ;THROW RETURN 55555 END CATCH
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

利点：THROWは、エラーメッセージが元のメッセージとまったく同じように生成されることです。 最初に2つのエラーメッセージがあった場合、両方のメッセージが再現されるため、この表現はさらに魅力的です。 他のすべてのエラーメッセージと同様に、THROWによって生成されたエラーは、外部CATCHハンドラーによってキャッチされ、再現されます。 CATCHハンドラがない場合、実行は中止されるため、この場合はRETURNステートメントは必要ありません。 （態度を変えた場合に備えて、そのままにしておくことをお勧めします。後で投げます）。



SQL Server 2012以降を使用している場合は、insert_dataとouter_spの定義を変更して、テストを再試行してください。 今回の結果は次のようになります。

 Msg 515, Level 16, State 2, Procedure insert_data, Line 5 Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails. Msg 2627, Level 14, State 1, Procedure insert_data, Line 6 Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

プロシージャ名と行番号は正確であり、混乱を招く可能性のある他のプロシージャ名はありません。 元のエラー番号も保存されました。



この時点で、あなたは自分自身に言うことができます：Microsoftは本当にコマンドに名前を付けましたか？ それはただ投げるだけではありませんか？ 実際、Books Onlineを見ると、セミコロンはありません。 しかし、セミコロンはすべきです。 公式には、前の式を区切りますが、これはオプションであり、T-SQL式で全員がセミコロンを使用するわけではありません。 さらに重要なことは、THROWの前にセミコロンをスキップした場合、構文エラーは発生しないことです。 しかし、これは式の動作に影響を与え、この動作は初心者には理解できません。 アクティブなトランザクションがある場合、元のトランザクションとはまったく異なるエラーメッセージが表示されます。 さらに悪いことに、アクティブなトランザクションがない場合、エラーは処理せずに静かに表示されます。 セミコロンをスキップするようなことは、そのようなばかげた結果をもたらさないはずです。 この動作のリスクを減らすために、コマンドは常にTHROW（セミコロン付き）と考えてください。



それを否定することはありません; THROWには利点がありますが、セミコロンがこのコマンドの唯一のtrapではありません。 使用したい場合は、少なくともこのシリーズの第2部を読むことをお勧めします。ここでは、チームに関する詳細を公開します。 ここまでは、error_handler_spを使用します。

4.3。 SqlEventLogの使用

エラーを処理する3番目の方法は、SqlEventLogを使用することです。これについては、第3部で詳しく説明します。 ここでは、短いレビューのみを行います。



SqlEventLogは、ストアドプロシージャslog.catchhandler_spを提供します。これは、error_handler_spと同じように機能します。error_xxx（）関数を使用して情報を収集し、エラーメッセージを表示して、それに関するすべての情報を保存します。 これに加えて、splog.sqleventlogテーブルにエラーを記録します。 使用しているアプリケーションのタイプによっては、このテーブルは非常に価値のあるオブジェクトになる可能性があります。



SqlEventLogを使用するには、CATCHハンドラーは次のようになります。

 BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION EXEC slog.catchhandler_sp @@procid RETURN 55555 END CATCH
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

@@ procidは、現在のストアドプロシージャのオブジェクト識別子を返します。 これは、SqlEventLogが情報をテーブルに記録するために使用するものです。 同じテストスクリプトを使用して、catchhandler_spを使用して作業の結果を取得します。

 Msg 50000, Level 16, State 2, Procedure catchhandler_sp, Line 125 {515} Procedure insert_data, Line 5 Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails. Msg 50000, Level 14, State 1, Procedure catchhandler_sp, Line 125 {2627} Procedure insert_data, Line 6 Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ご覧のとおり、エラーメッセージのフォーマットはerror_handler_spとは少し異なりますが、基本的な考え方は同じです。slog.sqleventlogテーブルに書き込まれたもののサンプルを次に示します。

logid	ログデート	errno	厳しさ	logproc	リネナム	msgtext
1	2015-01-25 22：40：24.393	515	16	insert_data	5	挿入できません...
2	2015-01-25 22：40：24.395	2627	14	insert_data	6	違反

SqlEventLogを試してみたい場合は、sqleventlog.zipファイルをダウンロードできます。インストール手順は、3番目の部分、SqlEventLogのインストールにあります。

5.最後のコメント

ストアドプロシージャでエラーとトランザクションを処理するための基本的なパターンを学習しました。完全ではありませんが、コードの90〜95％で機能するはずです。注意すべきいくつかの制限があります。

1. , , , .
2. , TRY-CATCH, RAISERROR .
3. Linked Server , .
4. INSERT-EXEC, , ROLLBACK TRANSACTION .
5. , error_handler_sp SqlEventLog, , SQL Server . ;THROW .

これらの状況については、このシリーズの他の記事で詳しく説明します。



終了する前に、トリガーとクライアントコードについて簡単に触れたいと思います。

トリガー

トリガーでのエラー処理の例は、1つの小さな詳細を除いて、ストアドプロシージャで使用されるものと大差ありません：RETURN式を使用しないでください（トリガーでRETURNを使用できないため）。



トリガーについては、トリガーを起動したチームの一部であり、BEGIN TRANSACTIONを使用していない場合でも、トリガー内のトランザクション内にいることを理解することが重要です。

時々、フォーラムで、立ち上げたチームが倒れた場合にロールバックしないトリガーを作成できるかどうかを尋ねる人がいます。答えは次のとおりです。これを確実に行う方法はないため、試してはいけません。これが必要な場合、可能であれば、トリガーをまったく使用せずに、別の解決策を見つけてください。第2部と第3部では、トリガーのエラー処理について詳しく説明します。

クライアントコード

データベースにアクセスできる場合、クライアントコードにエラー処理が必要です。つまり、どの呼び出しでも、何かが間違っている可能性があると常に想定する必要があります。エラー処理を正確に実装する方法は、特定の環境によって異なります。



ここでは重要なことにのみ注意を払います。SQLServerから返されるエラーへの応答は、孤立したトランザクションを回避するために、クエリの完了である必要があります。

 IF @@trancount > 0 ROLLBACK TRANSACTION
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これは、有名なタイムアウト期限切れメッセージ（SQL Serverからのメッセージではなく、APIからのメッセージ）にも適用されます。

6.最初の部分の終わり

これで、シリーズの3つのパートのうちの最初のパートの終わりです。エラー処理の問題をすばやく調査したい場合は、ここで読書を終了できます。さらに進むことに決めた場合は、パート2を読む必要があります。ここでは、SQL Serverでの紛らわしいエラーとトランザクション処理のジャングルの旅が実際に始まります。



...ストアドプロシージャの先頭に次の行を追加することを忘れないでください。

 SET XACT_ABORT, NOCOUNT ON