最終更新日: 7/20/2006
ホームデータベース> T-SQL エラー処理 : 次のトピック

T-SQL エラー処理

  1. @@ERROR
  2. RAISERROR
  3. sysmessage テーブル
  4. @@ROWCOUNT
  5. 例外処理
  6. トランザクション処理での注意
  7. ASP.NET 側における戻り値の取得方法
  8. ロックタイムアウト

@@ERROR

エラーがあった場合には通常、@@ERROR にエラー番号が格納されます。重要な処理を行うときには、@@ERROR をチェックするべきです。


INSERT INTO table1 ....
IF @@ERROR <> 0 BEGIN -- エラー処理
   PRINT 'ERROR!!'
   ...
END

しかし、残念なことに続行不能なエラーが発生した場合には 直ちに処理が中断 してしまうため、@@ERROR の評価までたどりつけない!という問題があります。

この問題を回避する方法:

次のように sp_executeeql を使ってSQL の呼び出しルーチンと (失敗したときに処理が中断されてしまう)  SQL の実行ルーチンを分離すればこの問題を回避できます。 sp_executesql の戻り値にはエラーコードが格納されるので、SQL の実行後、戻り値をチェックします。



DECLARE @res int
EXEC @res = sp_executesql N'INSERT...'
IF @res <> 0 BEGIN
    PRINT 'ERROR!!'
    ...
END

SQL Serverの実行時エラーの面倒なところは、エラーの種類によって、動作が異なるところです。
 
しかし、致命的なエラーもそうでないエラーも、発生したら、どちらもロールバックして終了させればよいような場合には、「SET XACT_ABORT ON」(デフォルトはOFF)を使用して、以下のように、実装することができます。

----- コード例 (SQL 2000 以前の T-SQL) ここから -----
SET XACT_ABORT ON
BEGIN TRANSACTION
 
DELETE [Order Details] WHERE OrderID IN
(SELECT OrderID FROM Orders WHERE CustomerID = 'ALFKI')

DELETE Orders WHERE CustomerID = 'ALFKI'
 
DELETE Customers WHERE CustomerID = 'ALFKI'
 
PRINT 'I got here'
-- Normally do a COMMIT TRANSACTION here.
-- But I do a ROLLBACK so I don't truly delete my test data.
ROLLBACK TRANSACTION
----- コード例 (SQL 2000 以前の T-SQL) ここまで -----

これでどのようなエラーが発生しても、その時点で、ロールバックして終了することになります。一方で、エラー発生時には、 即座にロールバックして終了するので、@@ERRORを判断するステートメントが実行されなくなります。そのため、上記例でも、@@ERRORを判断する
ステートメントが存在しません。
 
実際のシステムでは、致命的でないエラーの時には、ロジック上適切な後処理もせず、異常終了することは望ましくありません。従い、ほとんどの場合、 @@ERRORを何らかの形でトラップする、もしくは、sp_executesqlの戻り値をチェックすることになると思います。
 

RAISERROR

RAISERROR は通常はプロシージャを抜け (EXIT) ない。@@ERROR 変数が自動的にセットされる。

ただし RAISERROR コマンドの重要度(sevirity)をセットすることによって処理フローを変えることができる。た とえば非常にクリティカルなエラーの場合はそこで処理を停止するなど。し かし、すべての重要なエラーが処理を中断するわけではないので厄

sysmessage テーブル

sysmessage テーブルに事前定義されたエラー番号/メッセージを参照することがで きる。sysmessage にはフォーマット文字列が格納されている。

尚、自前でエラー番号をセットするときは 50001 から使うこと。システムが使用するエラー番号上限は 50000 であるから。

@@ROWCOUNT のチェック

DML ステートメント (SELECT, INSERT, UPDATE および DELETE) によってデータを変更した場合、影響を受けたレコード数は @@ROWCOUNT 変数に格納される。

これはエラー番号ではないが、狙った処理が適切に行われたかチェックするために利用することができる。

例外処理

従来の @@ERROR エラー番号チェック&アクションストラテジによるコードはこのようで した。

----- コード例 (SQL 2000 以前の T-SQL) ここから -----
BEGIN TRANSACTION

DELETE [Order Details] WHERE OrderID IN
(SELECT OrderID FROM Orders WHERE CustomerID = 'ALFKI')
IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION
    RETURN
END
DELETE Orders WHERE CustomerID = 'ALFKI'
IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION   
    RETURN
END
DELETE Customers WHERE CustomerID = 'ALFKI'
IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION
    RETURN
END
PRINT 'I got here'
-- Normally do a COMMIT TRANSACTION here.
-- But I do a ROLLBACK so I don't truly delete my test data.
ROLLBACK TRANSACTION
----- コード例 (SQL 2000 以前の T-SQL) ここまで -----

SQL Server 2005 では TRY/CATCH による構造化例外処理が導入されたので上記コードは次のように記述で きる。

----- コード例 (SQL 2005 の TRY/CATCH) ここから ------
BEGIN TRY
    BEGIN TRANSACTION
    DELETE [Order Details] WHERE OrderID IN
        (SELECT OrderID FROM Orders WHERE CustomerID = 'ALFKI')
    DELETE Orders WHERE CustomerID = 'ALFKI'
    DELETE Customers WHERE CustomerID = 'ALFKI'

    PRINT 'committing deletes'

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    RETURN
END CATCH
----- コード例 (SQL 2005 の TRY/CATCH) ここまで ------
 
CATCH ブロックでは以下のファンクションが利用可能:
 
ERROR_NUMBER()
ERROR_MESSAGE()
ERROR_SEVERITY()
ERROR_STATE()
ERROR_LINE()
ERROR_PROCEDURE()
 
CATCH ブロックでは @@error を調べるより ERROR_NUMBER を調べたほうが良い。

SQL 2000では、TRY/CATCHが使えません。そこで、ラベルによって例外処理部を作成し、まとめてエラーを処理するようにしてみました。以下がその例 です。

----- コード例 (SQL 2000 以前の T-SQL) ここから -----
BEGIN TRANSACTION
 
DECLARE @ErrCD INT, @RowCnt INT, @TabName SYSNAME
 
DELETE [Order Details] WHERE OrderID IN
(SELECT OrderID FROM Orders WHERE CustomerID = 'ALFKI')
SELECT @ErrCD = @@ERROR, @RowCnt = @@ROWCNT
 
IF @ErrCD <> 0 OR @RowCnt = 0 BEGIN
 SET @TabName = 'Order Details'
 GOTO ERR_RTN
END
 

DELETE Orders WHERE CustomerID = 'ALFKI'
SELECT @ErrCD = @@ERROR, @RowCnt = @@ROWCNT
 
IF @ErrCD <> 0 OR @RowCnt = 0 BEGIN
 SET @TabName = 'Orders'
 GOTO ERR_RTN
END
 

DELETE Customers WHERE CustomerID = 'ALFKI'
SELECT @ErrCD = @@ERROR, @RowCnt = @@ROWCNT
 
IF @ErrCD <> 0 OR @RowCnt = 0 BEGIN
 SET @TabName = 'Customers'
 GOTO ERR_RTN
END
 
-- Normally do a COMMIT TRANSACTION here.
-- But I do a ROLLBACK so I don't truly delete my test data.
ROLLBACK TRANSACTION
 
RETURN
 
ERR_RTN:
IF @ErrCD = 0
 RAISERROR('%sに対象行数がありませんでした',16,1,@TabName) WITH LOG
ELSE IF @ErrCD = 547 --制約違反の場合
 RAISERROR('%sに不正な値を登録しようとしました',16,1,@TabName) WITH LOG
ELSE
 RAISERROR('エラー発生!',16,1,@ErrCD) WITH LOG
 
ROLLBACK TRANSACTION
RETURN
----- コード例 (SQL 2000 以前の T-SQL) ここまで -----


このような方法で、ソースコードの冗長性を多少でも排除することができるという点において、SQL 2000 ユーザには有用だと思います。
 

トランザクション処理

エラー発生によって処理フローが変わり、BEGIN TRANSACTION によって開始したトランザクションを COMMIT も ROLLBACK もできない状況が起こりうる。

この問題に対処するには、XACT_ABORT トグルをオンにすればよい。これをセットするとランタイムエラーが発生したとき に、トランザクションを自動的にロールバックさせる。


SET XACT_ABORT ON

とはいえ、前述の TRY/CATCH による例外処理を利用すれば、通常 COMMIT も ROLLBACK もできない状況にはならないでしょう。逆に、例外処理機構とトランザクションを組み合わせる場合には、失敗時のロールバック方法に 選択肢が増える。呼び出されるコンテキストに注意し、「外部のトランザクションに入って呼び出された場合」 と 「プロシージャ内でトランザクションを開始した場合」 を区別して適切な処理を行うことができる。このとき Save Point を使う。
 

ADO.NET 側における戻り値の取得方法


ストアドプロシージャから呼び出し元へ処理結果をレポートする方法と して、RETURN ステートメントによる戻り値が利用できる。
 
CREATE PROCEDURE Foo
AS
BEGIN
     RETURN ( 777 );
END
 
RETURN 文による戻り値を ADO.NET 側で取得するには次のようにする。
 

DataTable t = new DataTable ();

using ( SqlConnection conn = new SqlConnection ( "<conn string>" ) ) {

    SqlCommand cmd = conn.CreateCommand ();
    cmd.CommandText = "foo";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue ( "@a" , i );
    cmd.Parameters.Add ( "@ret" , SqlDbType.Int );
    cmd.Parameters["@ret"].Direction = ParameterDirection.ReturnValue;

    SqlDataAdapter sda = new SqlDataAdapter ( cmd );

    sda.Fill ( t );

    return (int) cmd.Parameters["@ret"].Value;

}

戻り値用のパラメータエントリを作成し (名前は任意。ここでは @ret )、その Direction プロパティを ParameterDirection.ReturnValue にセット。処理終了後に Parameters["@ret"].Value として戻り値を参照する。
 
こ の値は、処理が成功したときにも使用可能。
 
IF @@ERROR <> 0 BEGIN
    PRINT 'ERROR!!";
    RETURN (666);
ELSE BEGIN
    RETURN 0;
END

ロックタイムアウト

DB上のエラーの代表格として、ロックタイムアウトがあると思います。当然、SQL Serverも、すでにロックしているリソースに、互換性のない
ロックを更にかけようとした場合には、待ちが発生します。そこで、ロックタイムアウトが発生したら、しばらく待って(30秒) リトライ(100回)をするようなロジックを考えてみました。

BEGIN TRANSACTION
--ロック待ちの際、待機しない
SET LOCK_TIMEOUT 0
 
DECLARE @RETRY_CNT TINYINT, @ErrCD INT
 
SET @RETRY_CNT = 1
 
PRINT 'START!'
 
WHILE @RETRY_CNT <= 100
BEGIN
 DELETE [Order Details] WHERE OrderID IN
 (SELECT OrderID FROM Orders WHERE CustomerID = 'ALFKI'')
--ロックタイムアウト発生
 IF @@ERROR = 1222 BEGIN
  WAITFOR DELAY '00:00:30'
  SET @RETRY_CNT = @RETRY_CNT + 1
  SET @ErrCD = @@ERROR
 END
 ELSE
  IF @@ERROR <> 0 BEGIN
   SET @ErrCD = @@ERROR
   GOTO ERROR_RTN
  END
  ELSE
  BREAK
END
 
IF @RETRY_CNT = 101 BEGIN
 RAISERROR('!!!error!!!',16,1)
SET @ErrCD = @@ERROR
 GOTO ERROR_RTN
END
 
-- Normally do a COMMIT TRANSACTION here.
-- But I do a ROLLBACK so I don't truly delete my test data.
ROLLBACK TRANSACTION
 
RETURN
 
ERROR_RTN:
IF @ErrCD = 50000
 RAISERROR('リトライしましたが、登録に失敗しました',16,1) WITH LOG
ELSE
 IF @ErrCD = 547 --制約違反の場合
  RAISERROR('不正な値を登録しようとしました',16,1) WITH LOG
 ELSE
  RAISERROR('エラー発生!:%D',16,1,@ErrCD) WITH LOG
ROLLBACK TRANSACTION
RETURN
GO

*WHILE 内のブロックは、SQL 2005であれば、TRY/CATCHを使ったほうが良いと思います。

ホームデータベース> T-SQL エラー処理 : 次のトピック

参考資料