MS SQL 2011-エラー処理

SQL Server 2011（Denali）の新しい便利なアドオンは、 Throw式です。 .Net開発者は、おそらくそれがどこでどのように使用されるかを推測しているでしょう。

この単語は、Try ... Catchコントロール構造と組み合わせて使用​​でき、実行時エラーに関する通知を送信できます。 例外が発生すると、プログラムは、例外を処理できる階層内の最も近いCatchブロックを検索します。 Catchブロック内でこの式を使用すると、エラー出力を変更できます。 さらに、スクリプトの任意の場所で例外を任意にスローできるようになりました。

次に、SQL Serverがバージョン2000からバージョン2011まで提供する例外をキャッチするさまざまな方法について、賛否両論を交えて説明します。



検討中のすべてのケースで、 tbl_ExceptionTestテーブルが使用されます。



マウスでデザイナーに穴を開けないようにするために、次のスクリプトを実行して、目的のテーブル（自動生成）を作成できます。

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'tbl_ExceptionTest' AND type = 'U') DROP TABLE tbl_ExceptionTest GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tbl_ExceptionTest]( [Id] [int] IDENTITY(1,1) NOT NULL, [Phone Number] [int] NOT NULL, CONSTRAINT [PK_tbl_ExceptionTest] PRIMARY KEY CLUSTERED ) 

次に、いくつかのレコードをテーブルに追加し、電話番号列に不適切なデータを入力したときに例外をスローしようとします。

SQL Server 2000（Sphinx）のエラー処理

グローバル変数@@ ERRORの使用

SQL Server 2000を使用していた頃に戻って、変数@@ Errorの使用は、エラーを処理する最も進歩的で効率的な方法であったことを思い出してください。 この変数は、最後に実行された式で発生した整数エラー値を返す役割を果たしました。 エラー値は正または負のいずれかであり、0のみが操作の成功を示しています。 各式の実行後に変数の値が変更されました。

アクションで@@エラーを使用して見てみましょう。

 --   #tblExceptionTest  ,   . If OBJECT_ID('tempdb..#tblExceptionTest') Is not null Begin Drop Table #tblExceptionTest End --    #tblExceptionTest Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null) --  Begin Transaction TranExcp__2000_@@Error --   --       @@ERROR Declare @ErrorNum int --      Declare @i int --   Set @i =1 --   While(@i <= 4) Begin --       null   Phone Number If(@i = 4) Begin Insert into #tblExceptionTest([Phone Number]) Values(null) Set @ErrorNum = @@ERROR End Else --      Begin Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678') End Set @i = @i +1 End --  while --   ,      If @ErrorNum <> 0 Begin Rollback Transaction TranExcp__2000_@@Error --      RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1) End --   Else If @ErrorNum = 0 Begin Commit Transaction TranExcp__2000_@@Error End --   Select * from #tblExceptionTest 

スクリプトの一般的な目的は、最後のレコードで意図的にエラーを発生させ、ローカル変数からその値を読み取ることです。 エラー値がゼロに等しくない場合、ユーザーに意味のある警告を表示します。 エラーがない場合は、結果を保存します。

以下に示すように、このスクリプトを実行するとエラーが発生します

メッセージ515、レベル16、状態2、行26列 '電話番号'、テーブル 'tempdb.dboに値NULLを挿入できません。＃TblExceptionTest _____ 000000000023'; 列はヌルを許可しません。 INSERTは失敗します。 ステートメントは終了しました。 メッセージ50000、レベル16、状態1、行43 [電話番号]にNULL値を挿入することは許可されていません

当然、トランザクション全体がロールバックされ、テーブルには何も入力されません。

@@エラーを使用することの欠点

• 変数@@ Errorの値は、リクエスト/コマンドの実行直後に確認する必要があります。
• @@エラーは絶えず変化しているため、エラーコードを保存および表示するために別の変数を開始する必要があります。
• エラーの論理的な意味を示す特別なエラーメッセージとともに、ユーザーにとって関心のない技術情報が表示されます。

@@ Errorの使用に関する詳細とニュアンスを知りたい場合は、@@ Errorに関する記事を参照することをお勧めします。

グローバル変数@@ TRANCOUNTを使用する

この変数は、変数がアクセスされたときに実行されているトランザクションの数を返します。 説明から、@@ ERRORとほぼ同じであることがすでに明らかです。 トランザクションの実行中に絶えず変化します。 この場合も、ローカル変数を使用して、興味深い時点で値を保存することになります。

各BEGIN TRANSACTION呼び出しは@@ TRANCOUNTを1増加させ、各COMMIT TRANSACTION呼び出しは1を減少させます。ROLLBACKTRANSACTIONは@@ TRANCOUNTを変更しません。 エントリは、値@@ TRANCOUNTが0に達したときにのみ入力されたと見なされます。

次の例で@@ TRANCOUNTの使用を検討してください。

 --   #tblExceptionTest ,    If OBJECT_ID('tempdb..#tblExceptionTest') Is not null Begin Drop Table #tblExceptionTest End --    Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null) --   Begin Transaction TranExcp__2000_@@TRANCOUNT --  --     @@TRANCOUNT Declare @TransactionCount int --  Declare @i int --   Set @i =1 --   While(@i <= 4) Begin --       null   Phone Number If(@i = 4) Begin Insert into #tblExceptionTest([Phone Number]) Values(null) Set @TransactionCount = @@TRANCOUNT End Else --      Begin Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678') End Set @i = @i +1 End --  while --   ,        If @TransactionCount <> 0 Begin Rollback Transaction TranExcp__2000_@@TRANCOUNT --    RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1) End --   Else If @TransactionCount = 0 Begin Commit Transaction TranExcp__2000_@@TRANCOUNT End --   Select * from #tblExceptionTest 

このスクリプトでは、クローズされたトランザクションの数に依存しています。 トランザクションはネストできるため、このメソッドには存在する権利があります。

@@ TRANCOUNTの詳細については、 MSDN にお問い合わせください。

グローバル変数@@ ROWCOUNTを使用する

この変数は、クエリ/コマンドの結果として変更された行の数を返します。

動作は前の2つと同じであるため、さらに分析するために中間結果をローカル変数に保存します。

例：

 If OBJECT_ID('tempdb..#tblExceptionTest') Is not null Begin Drop Table #tblExceptionTest End Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null) Begin Transaction TranExcp__2000_@@ROWCOUNT Save Transaction TranExcp__SavePoint Declare @RowCount int Declare @i int Set @i =1 While(@i <= 4) Begin If(@i = 4) Begin Insert into #tblExceptionTest([Phone Number]) Values(null) Set @RowCount = @@ROWCOUNT End Else Begin Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678') End Set @i = @i +1 End If @RowCount = 0 Begin Rollback Transaction TranExcp__SavePoint RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1) End Else If @RowCount <> 0 Begin Commit Transaction TranExcp__2000_@@ROWCOUNT End Select * from #tblExceptionTest 

この場合、1つのレコードがテーブルに挿入されると予想されますが、挿入されたレコードの数がゼロの場合、明らかに何かが順序どおりではありません。
@@ ROWCOUNTの使用の詳細については、MSDNを参照してください。

SQL Server 2005/2008のエラー処理（Yukon / Katmai）

SQL Server 2005を市場に導入し、SQL Server 2008でアイデアを開発した後、TSql開発者は新しいTry ... Catchブロックを使用します。 トランザクションコンテキストを失うことなく、例外をキャッチできるようになりました。

Try ... Catchブロックの使用例。

 If OBJECT_ID('tempdb..#tblExceptionTest') Is not null Begin Drop Table #tblExceptionTest End Begin TRY Create Table #tblExceptionTest (Id int identity, [Phone Number] varchar(10) not null) Begin Transaction TranExcpHandlingTest_2005_2008 Declare @i int Set @i =1 While(@i <= 4) Begin If(@i = 4) Begin Insert into #tblExceptionTest([Phone Number]) Values(null) End Else Begin Insert into #tblExceptionTest([Phone Number]) Values(cast(@i as varchar(2)) + '12345678') End Set @i = @i +1 End Commit Transaction TranExcpHandlingTest_2005_2008 End Try Begin Catch Begin Rollback Transaction TranExcpHandlingTest_2005_2008 RAISERROR ('Attempt to insert null value in [Phone Number] is not allowed',16,1) End End Catch Select * From #tblExceptionTest 

この例では、間接的な指示によってスクリプト実行エラーを判別するために、補助変数は使用されなくなりました。

スクリプトを実行すると、次のようなメッセージが表示されます。

メッセージ50,000、レベル16、状態1、行45 [電話番号]にnull値を挿入することは許可されていません

おそらくすでに気づいたように、今回はエラーメッセージに設定されたものだけが表示されました。 SQL Serverは、追加のユーザーを困らせるメッセージを表示していません。 実行可能コードはtryブロックに組み込まれ、エラー処理はcatchブロックに組み込まれます。 結果は、クリーンで明確なコードです。 目的のコードがすべてエラーなしで渡された場合、Catchブロックからのコードは呼び出されません。

最も重要なことは、Catchブロックは、エラーの原因を詳細に分析し、適切なレベルでユーザーに通知する機能のセットを提供します。 例外を解析するための関数：

• ERROR_NUMBER
• ERROR_SEVERITY
• ERROR_STATE
• ERROR_LINE
• ERROR_PROCEDURE
• ERROR_MESSAGE

これらの関数を使用して、前に説明したCatchスクリプトブロックの書き換えを試みます。

 Begin Catch --   Begin --   Rollback Transaction TranExcpHandlingTest_2005_2008 SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; End End Catch 

サーバーからこの応答を取得します。

RaiseError関数を使用する場合の欠点

1この関数がCatchブロックで示したものを思い出すと、問題の原因として行番号45を参照していることがわかります。



ただし、実際には、行番号24でエラーが発生したため、

#tblExceptionTest（[電話番号]）値に挿入（null）

ERROR_LINE（）関数は常にエラーが発生した実際の場所を返します。 新しい関数の動作を示す別の方法は次のとおりです。

 Begin Catch Begin Rollback Transaction TranExcpHandlingTest_2005_2008 DECLARE @errNumber INT = ERROR_NUMBER() DECLARE @errMessage VARCHAR(500) = 'Attempt to insert null value in [Phone Number] is not allowed' RAISERROR('Error Number: %d, Message: %s', 16, 1, @errNumber, @errMessage) End End Catch 

この場合、SQL Serverエンジンは次のメッセージを表示します。



このことから、RaiseErrorを使用すると、例外が発生したスクリプト内の実際の場所を示すことができなくなると結論付けることができます。

2 RaiseError関数の次の欠点は、同じ例外を再度呼び出して呼び出し階層を渡すことができないことです。 したがって、catchブロックを次のように書き換えると

 Begin Catch Begin Rollback Transaction TranExcpHandlingTest_2005_2008 RAISERROR(515, 16, 1) End End Catch 

受信したエラーメッセージは次のようになります。

メッセージ2732、レベル16、状態1、行46エラー番号515は無効です。 番号は13000〜2147483647でなければならず、50,000にすることはできません

この理由の1つは、新しいエラーメッセージを開始するために、 sysテーブルにエラー番号が含まれている必要があることです。 メッセージ

RaiseError関数のより詳細な調査については、以下を読むことをお勧めします。

• SQL Server 2005のRaiseErrorの詳細
• RaiseErrorを使用する

SQL Server 2011（Denali）のエラー処理

RaiseError関数の上記の欠点は、 新しい Throw コマンドで正常に克服できます。

前に指摘したRaiseError関数の最初の欠点は、エラー発生の正確な行を参照できないことです。 エラーが発生した場所からどれくらい離れているかを考えて、Throwコマンドを使用しています。

Throwコマンドを使用して、Catchブロックを書き換えます。

 Begin Catch Begin Rollback Transaction TranExcpHandlingTest_2011; THROW End End Catch 

出力は次のようになります。



これはまさにエラーが発生した場所です。 まあ、それは今のところうまく動作します。

2番目の欠点は、RAISE ERRORがsys.messagesテーブルに格納されているエラー番号を予期しているため、RaiseError関数が例外を再発生できないことです。 Throwコマンドは、エラー番号がsys.messagesシステムテーブルの範囲にあることを想定していませんが、50,000から2147483647までの範囲に設定できます。

繰り返しますが、新しい知識に従ってCatchブロックを変更します。

 Begin Catch Begin Rollback Transaction TranExcpHandlingTest_2011; THROW 50001,'Attempt to insert null value in [Phone Number] is not allowed',1 End End Catch 

例外の結果は

メッセージ50001、レベル16、状態1、行45 [電話番号]にnull値を挿入することは許可されていません

現在、SQL Serverはエラーをキャッチする多くの方法を提供していますが、これまでのところ、Try ... Catchブロックを使用してすべてのエラーをキャッチできるわけではありません。 例：

• 構文エラーは、SSMSのクエリエディターによってキャッチされます。
• 間違ったオブジェクト名

次のスクリプトを実行のために送信しようとした場合：

 Begin Try --   tblInvalid Insert Into tblInvalid(Id,DOB) Values(1,DATEADD(year,1,'2011-02-26')) End Try Begin Catch --  THROW End Catch 

次の計画のエラーメッセージが表示されます。

メッセージ208、レベル16、状態0、行3無効なオブジェクト名 'tblInvalid'。

これらのタイプのエラーを傍受することはほとんど不可能であることがわかります。

しかし。 いつものように、あなたが望むものを実現するための小さなトリックがあります。 主なアイデアは、2つのストアドプロシージャを作成し、Try ... Catchブロックで他の1つを呼び出し、例外をキャッチすることです。 仮定を証明するために、次のスクリプトを実験に使用します。

 --   ,  ,   If Exists (Select * from sys.objects where name = 'usp_InternalStoredProc' and type = 'P') Drop Procedure usp_InternalStoredProc Go --     Create Procedure usp_InternalStoredProc As Begin Begin Transaction TranExcpHandlingTest_2011 Begin Try --     Insert Into tblInvalid(Id,DOB) Values(1,DATEADD(year,1,'2011-02-26')) --   Commit Transaction TranExcpHandlingTest_2011 End Try Begin Catch If @@TRANCOUNT > 0 Rollback Transaction TranExcpHandlingTest_2011 Print 'In catch block of internal stored procedure.... throwing the exception'; --   THROW End Catch End Go --       --   ,  ,   If Exists (Select * from sys.objects where name = 'usp_ExternalStoredProc' and type = 'P') Drop Procedure usp_ExternalStoredProc Go --     Create Procedure usp_ExternalStoredProc As Begin Begin Try --    Exec usp_InternalStoredProc End Try Begin Catch Print 'In catch block of external stored procedure.... throwing the exception'; SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; THROW End Catch End Go --    Exec usp_ExternalStoredProc 

ExternalStoredProcプロシージャを開始すると、次のメッセージが表示されます。

 In catch block of external stored procedure.... throwing the exception (1 row(s) affected) Msg 208, Level 16, State 1, Procedure usp_InternalStoredProc, Line 8 Invalid object name 'tblInvalid'. 

結果パネルには次のデータが表示されます。



必要なもの！

ここで、コードの仕組みについて少し説明します。 usp_InternalStoredProcとusp_ExternalStoredProcの 2つのストアドプロシージャがあります 。 usp_InternalStoredProcでは、存在しないテーブル#tblInnerTempTableにレコードを挿入しようとしますが、その結果、例外が発生します。この例外は、外部プロシージャにある外部Catchブロックによってキャッチされます。

さらに、エラーの行とテキストは私たちの期待に完全に準拠しており、正確な場所を示しています。

外部プロシージャの今後のTHROW式をセミコロンで閉じることを忘れないことが非常に重要です。 THROWは新しいコマンドのセットでなければなりません。 そうでない場合、エラーが発生します

「THROW」付近の構文が正しくありません。

THROWの詳細については、MSDNを参照してください。

サイクルからの転送：
MS SQL Server 2011： スタンドアロンデータベース 、 新しいシーケンスオブジェクト 、 オフセットステートメント 、 エラー処理 、 結果セット構成 、 SSMSの新機能 。