REFERENCE

How to implement error handling in SQL Server

エラーハンドリング概要

SQLserverによるエラーハンドリングはT-SQLコードによって可能。例えば私たちにとって好ましくない現象が起こった際に、それを正しい形に記述しなおすことができる。SQLserverにおけるエラーハンドリングは私たちがコードを修正できるように、問題が起こった時にそれをログとして蓄積する。SQLserverにおける技術的なエラーメッセージを理解することが困難なことは明白なため、エラーハンドリングはSQL言語においてエラーを翻訳することすら行う。幸いなことに、ユーザー、デベロッパー、その他のあらゆる人にとって、エラーメッセージをより意味深い形で翻訳することができるのだ。

この記事では、私たちはTRY...CATCHステートメントについてより詳細に見ていくことにする。このシンタックス、これがどのようなもので、どのように動き、エラーが発生した際に何が行われるのかをつまびらかにする。さらに、SQLserver上における基本的なエラーハンドリングの記法を用いた具体例によって説明する。これはとてもシンプルであり、かつ構造化された方法であり、一度利用方法を覚えたらあらゆる場面で非常に有効なものとなり得る。

さらに、ここではRAISEERRORについても言及する。これは煩雑なエラーメッセージをユーザーにとって理解できる、もう少し意味深いものに翻訳するすぐれた方法である、カスタムエラーメッセージを作成するために利用される。

TRY...CATCHを利用したエラーハンドリング

ここに次のようなシンタックスがある。これは利用するのにとてもシンプルな形である。コードを記述するのに二つのブロックから成る。

BEGIN TRY  
     --code to try 
END TRY  
BEGIN CATCH  
     --code to run if error occurs is generated in try
END CATCH

BEGIN TRY とEND TRYの間には私たちがエラーを観測したいと思っているコードを記述する。そして、このTRYステートメントの中でエラーが発生した時、このコントロールはすぐさまCATCHステートメントに移行し一行ずつ実行される。

CATCHステートメントの中で、私たちはエラーを修正、報告、あるいは蓄積を行うことが可能であり、私たちは何が起こったのか、ログしたのは誰か、多くの利用価値のあることを把握することができる。私たちはCATCHステートメントの中だけで可能な特殊なデータにアクセスすることさえ可能なのである。

ERROR_NUMBER エラーの内部番号を返す
ERROR_STATE ソースについての情報を返す
ERROR_SEVERITY 情報エラーからDBAが修正可能なエラーなどあらゆる情報を返す
ERROR_LINE エラーが発生した行を返す
ERROR_PROCEDURE 蓄積されたプロシージャあるいはファンクションの名前を返す
ERROR_MESSAGE もっとも基本的な情報、エラーのテキストメッセージを返す

これはSQLserverにおけるエラーハンドリングに行う際に必要なすべてである。すべてシンプルなTRY CATCHステートメントで実行可能であり、トリッキーになり得るのはトランザクションを処理する時のみである。なぜか？BEGIN TRANSACTIONを記述する時、それはいつもCOMMITあるいはROLLBACKトランザクションで終わる必要があるからである。BEGINしたがCOMMITまたはROLLBACKを終える前にエラーが発生した時問題となる。この特殊なケースでは、ロールバックかコミットを行うことか可能か判断するためにトランザクションがコミット可能なステートか否かをチェックするためにCATCHステートメントで利用する特殊な関数を用いることができる。

SQL Server Management Studio上で操作可能なふたつの基本的なSQL Serverエラーハンドリングを下記に示す。

USE AdventureWorks2014
GO
-- Basic example of TRY...CATCH
 
BEGIN TRY
-- Generate a divide-by-zero error  
	SELECT
		1 / 0 AS Error;
END TRY
BEGIN CATCH
	SELECT
		ERROR_NUMBER() AS ErrorNumber,
		ERROR_STATE() AS ErrorState,
		ERROR_SEVERITY() AS ErrorSeverity,
		ERROR_PROCEDURE() AS ErrorProcedure,
		ERROR_LINE() AS ErrorLine,
		ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

上記をどのように記述してどう動くかの例を下記に示す。ここではただ1を0で除する操作をBEGIN TRYに記述しているが、もちろんこれはエラーを生じさせる。その際にはすぐさまCATCHブロックに移行して、前述した組み込み関数を用いたプロパティがセレクトされる。

ふたつのSELECTステートメントが存在するのでふたつのグリッドに結果が反映されている。
ひとつめは1/0というコードがエラーを生じさせている。ふたつめは、エラー発生後に移行したコントロールでの結果を反映しており、左から、ErrorNumber、ErrorState、ErrorSeverity、ErrorProcedure(ここではNULL)、ErrorLine、そしてErrorMessageの結果を見ることができる。

もう少し意味のあることをやってみる。これらのエラーをトラックするのがイケてるだろう。エラーを起こしやすいコードはとにかく監視、少なくとも記録を残すべきだ。私たちはログとして記録されたテーブルにトリガーをかけでき、もう少し建設的なやり方では、エラーが起きた時にそれを通知するようメールアカウントをセットアップすることさえできる。

もしデータベースのメールに精通していないようであれば、メールシステムのより詳細な情報源として次の記事を参照されたい。
How to configure database mail in SQL Server

下記のスクリプトではトラッキングデータを蓄積するためにDB_Errorsというテーブルを作成している。

-- Table to record errors
 
CREATE TABLE DB_Errors
				 (ErrorID        INT IDENTITY(1, 1),
				  UserName       VARCHAR(100),
				  ErrorNumber    INT,
				  ErrorState     INT,
				  ErrorSeverity  INT,
				  ErrorLine      INT,
				  ErrorProcedure VARCHAR(MAX),
				  ErrorMessage   VARCHAR(MAX),
				  ErrorDateTime  DATETIME)
GO

ここではシンプルなIDENTITYカラムを利用しており、エラーを発生させたのが誰なのかを確認するためのUserNameと、前述したシンプルな組み込み関数の情報から構成されている。

ここで、データベースによるカスタムストアドプロシージャを修正してエラーハンドラーを設置する。

ALTER PROCEDURE dbo.AddSale @employeeid INT,
									 @productid  INT,
									 @quantity   SMALLINT,
									 @saleid     UNIQUEIDENTIFIER OUTPUT
AS
SET @saleid = NEWID()
	BEGIN TRY
		INSERT INTO Sales.Sales
				 SELECT
					 @saleid,
					 @productid,
					 @employeeid,
					 @quantity
	END TRY
	BEGIN CATCH
		INSERT INTO dbo.DB_Errors
		VALUES
			(SUSER_SNAME(),
			ERROR_NUMBER(),
			ERROR_STATE(),
			ERROR_SEVERITY(),
			ERROR_LINE(),
			ERROR_PROCEDURE(),
			ERROR_MESSAGE(),
			GETDATE());
	END CATCH
GO

この変更では、ストアドプロシージャ内のステートメントにただエラーハンドリングがラップされる。このストアドプロシージャを呼び出すと下記のようになる。

このステートメントはレコードがインサートされたことを示している。

しかしながら、仮に同じパラメータを引数として再度上記のストアドプロシージャを呼び出すと、結果グリッドには異なる内容が出力される。

この時、結果グリッドにはふたつのインディケイターが示される。

・ 0 rows affected この行は、Salesテーブルにはなにもインサートされなかったことを示している
・ 1 row affected この行は、新しく作成されたログテーブルにインサートされたことを示している。

ここでエラーテーブルを参照し、何が起こったのかを確認する。

ログとして記録すためにセットした情報をすべて参照することができる。ここではERROR PROCEDUREも反映しているが、もちろんSQLserverにはエラーを発生させた際の親切なテクニカルメッセージを出力する機能がある。

Violation of PRIMARY KEY constraint ‘PK_Sales_1’. Cannot insert duplicate key in object ‘Sales.Sales’. The duplicate key value is (20).

これは非常にぎこちない事例だが、重要なのは実際の業務において無効なデータが引き渡されるのは非常によくある事象であることだ。例えば、SalesテーブルとEmployeeテーブルの間に外部キーが設定されている時には存在しないemployeeIDを渡す。Salesテーブルにレコードを挿入するためにEmployeeが存在している必要があるからだ。この場合、外部キー制約に違反している。

一般的にはこのような場合エラーを解消することそのものが目的なのではなく、私たちは少なくとも何が問題だったのかをレポートして、そしてそれを覆いの下にログとして蓄積することを望んでいる。現実世界では、ストアドプロシージャにを利用したアプリケーションがあったら、会社つしゃはおそらくよりうまくエラーハンドリングを記述することができる。いつエラーが発生したのかを知れるからだ。エラーバッグをユーザーやアプリケーションに返すこともここでは賢明な判断だ。これはRAISERRORファンクションを加えることによって私たちはエラーを発生させたバージョンを棄却することができる。

例えば、私たちが存在しないemployIDを入力することが頻出する場合にはルックアップを実行することが可能である。このルックアップではemployIDが存在しているのか否かを確認し、存在しない場合には正確なエラーを返す。もっと悪い場合には、事前に予期されておらずそれがどんなエラーであるかわからない場合には、それをそのまま返すことができる。

カスタムエラーの作成

トランザクションにおける特殊な処理について短く言及した、ここでは、それがどのように実行されるのか簡単な例を示す。前述と同じ処理を行うが、ここではINSERTステートメントをTRANSACTIONで囲む。

ALTER PROCEDURE dbo.AddSale @employeeid INT,
									 @productid  INT,
									 @quantity   SMALLINT,
									 @saleid     UNIQUEIDENTIFIER OUTPUT
AS
SET @saleid = NEWID()
	BEGIN TRY
		BEGIN TRANSACTION
		INSERT INTO Sales.Sales
				 SELECT
					 @saleid,
					 @productid,
					 @employeeid,
					 @quantity
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		INSERT INTO dbo.DB_Errors
		VALUES
	(SUSER_SNAME(),
	 ERROR_NUMBER(),
	 ERROR_STATE(),
	 ERROR_SEVERITY(),
	 ERROR_LINE(),
	 ERROR_PROCEDURE(),
	 ERROR_MESSAGE(),
	 GETDATE());
 
-- Transaction uncommittable
		IF (XACT_STATE()) = -1
			ROLLBACK TRANSACTION
 
-- Transaction committable
		IF (XACT_STATE()) = 1
			COMMIT TRANSACTION
	END CATCH
GO

すべてがBEGIN TRANSACTIONの内部で正常に実行された時、Salesレコードに引数がインサートされ、COMMITされる。しかしCOMMITが実行される前にエラーが発生した場合、即CATCHに移行する。――問題は、すべてがCOMMITあるいはROLLBACKされたか否かを、私たちがどうやって知るのかだ。

エラーが深刻でなく、COMMIT可能なステートである場合にはトランザクションをCOMMITすることができる。しかし何かが問題であり、COMMIT不可能なステートである場合には、トランザクションをROLLBACKすることができる。この動作は、トランザクションのステートを報告するXACT-STATEファンクションを実行、分析することでおこなうことができる。

このファンクションは次の値を返す。

1 トランザクションはCOMMIT可能である　
-1 トランザクションはCOMMIT不可能でありROLLBACKするべきである　
0 保留中のトランザクションは存在しない

ここでCATCHはトランザクションを開始、COMMIT、ROLLBACKを実行しないため、CATCHステートメントの内部で上記の動作を記述する必要がある。

ここで、前述と同じようにストアドプロシージャを実行すると、EmployIDの引数が無効となり、前回と同じくテーブルにエラー内容がインサートされる。

インサートされなかったことを確認するために、シンプルなSELECTクエリを記述し、SalesテーブルからEmployIDが20であるすべてのレコードを選択する。

どのように自身のカスタムエラーメッセージを作成することができるのかを見ていく。起こり得る状況を予め知っているのはよいことだ。前述したように、誰かが無効なEmployIDを渡すのは起こり得ることだ。この場合には充分事前にチェックすることが可能であり、問題が起こった場合にはemployIDが存在しないといったカスタムメッセージを生成することができる。これはストアドプロシージャを何度も変更するよりも容易に実行でき、TRYブロックの中にルックアップを加えることもできる。

ALTER PROCEDURE dbo.AddSale @employeeid INT,
									 @productid  INT,
									 @quantity   SMALLINT,
									 @saleid     UNIQUEIDENTIFIER OUTPUT
AS
SET @saleid = NEWID()
	BEGIN TRY
	IF (SELECT COUNT(*) FROM HumanResources.Employee e WHERE employeeid = @employeeid) = 0
		  RAISEERROR ('EmployeeID does not exist.', 11, 1)
		
		INSERT INTO Sales.Sales
				 SELECT
					 @saleid,
					 @productid,
					 @employeeid,
					 @quantity
	END TRY
	BEGIN CATCH
		INSERT INTO dbo.DB_Errors
		VALUES
	(SUSER_SNAME(),
	 ERROR_NUMBER(),
	 ERROR_STATE(),
	 ERROR_SEVERITY(),
	 ERROR_LINE(),
	 ERROR_PROCEDURE(),
	 ERROR_MESSAGE(),
	 GETDATE());
 
	 DECLARE @Message varchar(MAX) = ERROR_MESSAGE(),
				@Severity int = ERROR_SEVERITY(),
				@State smallint = ERROR_STATE()
 
	 RAISEERROR (@Message, @Severity, @State)
	END CATCH
GO

COUNTがゼロになる時、それはemployeeテーブルの中に引数のIDが存在していないことを意味する。その時RAISERRORを呼び出し、ユーザーが定義したメッセージや深刻度、ステートをセットする。これは問題を理解するためにストアドプロシージャを利用する誰かにとっては、SQLが投げる技術的なメッセージ、ここでは外部キーの違反であるが、それを見るよりもより容易なことである。

ストアドプロシージャにおけるCATCHブロックに最後の変更を加える。もし他のエラーが起こった時、再度RAISEERRORを呼び出して何が起こったのかを正確に返すことができる。それがすべての変数と巻数を定義した理由だ。この方法では単にログに蓄積するだけでなくアプリケーションまたはユーザーにレポートすることができる。

前回と同じコードを実行すると、ログを記録し、employeeIDが存在しないことが結果として示される。

別に言及すべき事柄としては、エラーメッセージやその深刻さ、ステートを事前に定義することが可能なことだ。エラーメッセージを加えるために用いられるsp_addmessageというストアドプロシージャがあり、これは複数の場所でメッセージを呼ぶ必要がある場合に便利である。私たちは要素を再入力するのではなく、ただRAISEERRORを用いてメッセージナンバーを渡せばよい。下記のように実行すると、このエラーがSQL Serverに加えられる。

つまり私たちが前回行った方法とは違い、ただRAISEERRORを呼び出してエラーナンバー渡すのである。これは下記のように動作する。

このsp_dropmessageは勿論ユーザーに定義された特殊なエラーメッセージを削除するために用いられる。私たちは下記のクエリを実行することでSQL Serverにおけるすべてのエラーメッセージを確認することができる。

SELECT * FROM master.dbo.sysmessages

非常に多くのものが存在するが、一番上にはカスタムされたものがある。

この記事があなたにとって有益なものであることを願っている。読んでくれてありがとう。