例外処理の方法をまとめておきたかったので、SQL Server 2005 以降でサポートされている例外処理の記述サンプルを掲載します。
確認環境
- SQL Server 2005 SP2
- 実行環境 Windows Server 2003
- 使用データベース AdventureWorks
例外処理の解説については下記リンクを参照
Transact-SQL での TRY...CATCH の使用
http://msdn.microsoft.com/ja-jp/library/ms179296.aspx
Transact-SQL のエラー情報の取得
http://msdn.microsoft.com/ja-jp/library/ms179495.aspx
1. 例外処理サンプル1
TRY .. CATCH を使用したT-SQLサンプルです。重複エラーを発生させるために、IDENTITY 列に値を設定するためにSET IDENTITY_INSERT を故意に設定してあります。サンプルでは例外をキャッチして、例外情報を取得し、RAISERROR関数で再度例外をスローするようにしています。
USE [AdventureWorks] GO /* TRY..CATCH を使用した例外処理 参考リンク Transact-SQL での TRY...CATCH の使用 http://msdn.microsoft.com/ja-jp/library/ms179296.aspx Transact-SQL のエラー情報の取得 http://msdn.microsoft.com/ja-jp/library/ms179495.aspx */ SET NOCOUNT ON; -- 重複エラーを出すために故意に設定 SET IDENTITY_INSERT AdventureWorks.Sales.SalesReason ON; GO BEGIN TRY INSERT INTO Sales.SalesReason(SalesReasonID, Name, ReasonType, ModifiedDate) VALUES (1, 'Name1', 'ReasonType1', GetDate()); END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000) , @ErrorNumber INT , @ErrorSeverity INT , @ErrorState INT , @ErrorLine INT , @ErrorProcedure NVARCHAR(126) ; -- TRY...CATCH 構造の CATCH ブロックが実行される原因となったエラーのメッセージ テキストを返します。 SET @ErrorMessage = ERROR_MESSAGE() -- TRY...CATCH 構造の CATCH ブロックの実行を引き起こしたエラーのエラー番号を返します。 -- @@error に対応する関数 SET @ErrorNumber = ERROR_NUMBER() -- TRY...CATCH 構造の CATCH ブロックの実行を引き起こしたエラーの重大度を返します。 SET @ErrorSeverity = ERROR_SEVERITY() -- TRY...CATCH 構造の CATCH ブロックが実行された原因となるエラーの状態番号を返します。 SET @ErrorState = ERROR_STATE() -- TRY...CATCH 構造の CATCH ブロックの実行を引き起こすエラーが発生した行番号を返します。 SET @ErrorLine = ERROR_LINE() -- TRY...CATCH 構造の CATCH ブロックの実行原因となったエラーが発生した、 -- ストアド プロシージャまたはトリガの名前を返します。 SET @ErrorProcedure = ERROR_PROCEDURE() -- 実行確認用 PRINT N'ERROR_NUMBER() '; PRINT @ErrorNumber; PRINT N'ERROR_MESSAGE() '; PRINT @ErrorMessage; PRINT N'ERROR_SEVERITY() '; PRINT @ErrorSeverity; PRINT N'ERROR_STATE() '; PRINT @ErrorState; PRINT N'ERROR_LINE() '; PRINT @ErrorLine; PRINT N'ERROR_PROCEDURE() '; PRINT @ErrorProcedure; RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH
2. セーブポイントを使用した、例外処理
呼び出し側のトランザクションの有無によってトランザクションのロールバックか、セーブポイントへのロールバックかを決定する処理を追加したT-SQLのサンプルを掲載します。サンプルではさらに、XACT_STATE() を使用してコミット可否を判定するようにしています。
USE [AdventureWorks] GO /* セーブポイントを使用した、例外処理 呼び出し側のトランザクションの有無によって トランザクションのロールバックか、セーブポイントへのロールバック かを決定する。 XACT_STATE() を使用してコミット可否を判定する 参考リンク Transact-SQL での TRY...CATCH の使用 http://msdn.microsoft.com/ja-jp/library/ms179296.aspx Transact-SQL のエラー情報の取得 http://msdn.microsoft.com/ja-jp/library/ms179495.aspx */ SET NOCOUNT ON; -- 重複エラーを出すために故意に設定 SET IDENTITY_INSERT AdventureWorks.Sales.SalesReason ON; -- 制約エラーもコミットできないエラーとする SET XACT_ABORT ON; -- ロックのタイムアウトを0秒に SET LOCK_TIMEOUT 5000; GO -- トランザクション数をカウント DECLARE @tc AS INT; SET @tc = @@trancount; IF @tc > 0 SAVE TRANSACTION S1; ELSE BEGIN TRANSACTION BEGIN TRY INSERT INTO Sales.SalesReason(SalesReasonID, Name, ReasonType, ModifiedDate) VALUES (1, 'Name1', 'ReasonType1', GetDate()); INSERT INTO Sales.SalesReason(SalesReasonID, Name, ReasonType, ModifiedDate) VALUES (2, 'Name1', 'ReasonType1', GetDate()); COMMIT TRANSACTION END TRY BEGIN CATCH PRINT N'例外発生' DECLARE @ErrorMessage NVARCHAR(4000) , @ErrorNumber INT , @ErrorSeverity INT , @ErrorState INT , @ErrorLine INT , @ErrorProcedure NVARCHAR(126) ; -- TRY...CATCH 構造の CATCH ブロックが実行される原因となったエラーのメッセージ テキストを返します。 SET @ErrorMessage = ERROR_MESSAGE() -- TRY...CATCH 構造の CATCH ブロックの実行を引き起こしたエラーのエラー番号を返します。 -- @@error に対応する関数 SET @ErrorNumber = ERROR_NUMBER() -- TRY...CATCH 構造の CATCH ブロックの実行を引き起こしたエラーの重大度を返します。 SET @ErrorSeverity = ERROR_SEVERITY() -- TRY...CATCH 構造の CATCH ブロックが実行された原因となるエラーの状態番号を返します。 SET @ErrorState = ERROR_STATE() -- TRY...CATCH 構造の CATCH ブロックの実行を引き起こすエラーが発生した行番号を返します。 SET @ErrorLine = ERROR_LINE() -- TRY...CATCH 構造の CATCH ブロックの実行原因となったエラーが発生した、 -- ストアド プロシージャまたはトリガの名前を返します。 SET @ErrorProcedure = ERROR_PROCEDURE() IF @tc = 0 -- トランザクションを開始した場合 BEGIN IF (XACT_STATE()) <> 0 BEGIN PRINT N'ロールバックを行います' ROLLBACK TRANSACTION END END ELSE -- セーブポイントを設定した場合 BEGIN IF (XACT_STATE()) = 1 BEGIN PRINT N'本処理中の処理のみロールバックします' ROLLBACK TRANSACTION S1; END ELSE IF (XACT_STATE()) = -1 BEGIN -- ロールバックできない状態. 処理は呼び出し側に委任する PRINT N'トランザクションはコミットできない状態です。' END END -- 呼び出し元に委任 RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH
3. まとめ
解説は以上です。SQL Server 2000 の場合は、@@error の内容で確認していましたが、構造的でわかりやすい例外処理ができるようになったと思います。