QA@IT
この質問・回答は、@ITの旧掲示板からインポートされたものです。

SQLServer2000 ストアドのエラー処理

ストアドプロシージャの実行中に致命的でないエラー
(一意制約違反等)が発生した場合、
戻り値が戻らずに、サーバエラーが出力されます。

ソースは以下のようになっております。
--------------------------------------------------
CREATE PROCEDURE XXXXX
@rtnmsg varchar(100)='' output
AS
@rtcd int
Set Nocount on
Begin Transaction

・・・・

INSERT INTO ・・・・
SELECT @rtcd = @@ERROR
if @rtcd<>0 GoTo ERROR_HANDLER

・・・・

Commit Transaction
return 0

ERROR_HANDLER:
    Rollback Transaction
    select @rtmsg = 'XXXXXX'
    return @rtcd

--------------------------------------------------

クエリアナライザでステップ実行すると、
SQL実行後に、"@@ERROR"を判定し、"ERROR_HANDLER"以下を実行しております。
サーバエラーを出力させずに、
戻り値を正しく戻すにはどのようにすればよろしいのでしょうか?

お知恵を拝借できればと存じます。

質問者:はるぱぱ

回答

ストアドプロシージャ内でエラーが発生した場合に、
エラーを出力させずに処理してしまう実装はどうかと思いますが・・。
ましてや、一意制約違反などは、私にとっては重大なエラーだと思うんですが・・。

例に示しているストアドプロシージャでは、
エラーが起こらないようにINSERTクエリを実行する前に
挿入しようとしているデータが既にテーブルに存在しているかどうかを
先に調べればいいと思います。
私なら以下のようにやります。

If Not Exists(Select * From TableA)
Begin
--データが存在していない場合
Insert Into TableA ・・・
End
Else
Begin
--データが既に存在している場合
End

また、どうしてもサーバーから返されるエラーを無視したければ、
try-catchで例外を処理してあげればいいと思います。

投稿者:sou

編集 履歴 (0)

エラーが起こらないようにINSERTクエリを実行する前に挿入しようとしているデータが既にテーブルに存在しているかどうかを先に調べればいいと思います。

先に調べたらまずいと思います。同時に調べるか、もしくはロックをかけつつ調べないと。if 文での条件判定が終わった直後に他のセッションが TableA にデータを挿入しているかもしれませんよ?

(方法1) if 文での検査用 select 文に SERIALIZABLE を付ける。分離レベルが REPEATABLEREAD 以下だとファントムの挿入を許してしまうので SERIALIZABLE が必要になります。この方法は同時実行性能を大きく低下させることになります。



if not exists(select * from TableA with (SERIALIZABLE)) begin
    insert into TableA ...
end

(方法2) 検査と同時に挿入をおこなう。挿入しようとしている値 'hoge' が存在していなければ、select が行を返すように構成します。



insert into TableA ...
select 'hoge'
where 'hoge' not in (select key from TableA)

それから元質問のほうですが・・・。エラーメッセージは表示されますが、それでも ERROR_HANDLER へ分岐し戻り値 2627 などが返るはずです。そうならないのは、どこかで SET XACT_ABORT ON を設定しているからではありませんか? XACT_ABORT が有効になっている場合、ステートメントでエラーが発生するとそこでトランザクションがロールバックして終了しますので、ステートメントの次行で @@ERROR をチェックするようなコーディングはできなくなります。

投稿者:未記入

編集 履歴 (0)

SUOさん 未記入さん
回答ありがとうございます。

表現が足りませんでした。
このストアドは、VB.NETから起動するので、
実行自体は正常終了させたいのです。
(VBでリターンコードを判定しエラー処理を行う)
問題は、上記のエラーが発生するとVB側で例外エラーが発生してしまうことです。
また、SET XACT_ABORT ON の設定はしておりません。

ただ、なぜかVBで例外エラーをトラップすると、
OUTのパラメータは正しく格納されているのようなので対応はできるのですが、
書籍やMSDNのサンプルを調べ、
上記のようなプログラムが基本的なエラー処理と理解しておりましたので、
どうも腑に落ちないのです。
SET XACT_ABORT ONの逆のように、
エラーを無効にする設定があるのかと思い質問させて頂きました。

投稿者:はるぱぱ

編集 履歴 (0)

こんにちは

そもそも、サーバーエラーとはどんなエラーが出ているのでしょうか?
戻り値は正しく返っているけど、受け取り側でなんらかを行って、エラーになっている可能性は無いのでしょうか?
受け取り側のコードも(可能な範囲で)公開されるとよいかもしれません。

投稿者:THREE-ONE

編集 履歴 (0)

THREE-ONEさん 書き込みありがとうございます。

サーバエラーは一意制約違反であれば以下のようなもので、
クエリアナライザで実行すると、@RETURN_VALUE = XXXという出力がありません。

--以下エラー--
サーバー : メッセージ 2627、レベル 14、状態 1、プロシージャ XXXXXXX、行 XXXXX
[Microsoft][ODBC SQL Server Driver][SQL Server]PRIMARY KEY 違反、制約 'XXXX_TBL': オブジェクト 'XXXX_TBL' には重複したキーは挿入できません。

VBでのエラーオブジェクトのメッセージも同様です(PRIMARY KEY 違反、・・・)。

VBでのストアドのコールは以下のようにしています。
最初のパラメータ(添え字"0")のDirectionがReturnValueです。
こちらはステップ実行すると、ExecuteNonQueryの実行でExceptionが発生します。
前のメッセージにも書きましたが、
Exceptionが発生してもOUTのパラメータはきちんと取得できるので
Catch以降の処理で対応は可能ですが、
SQLの実行でエラーが発生した場合に
ストアド自体の実行を正常終了とする実装方法を知りたいのです。
ちなみに今回TSQLでのストアドの作成は初めてですが、
PL/SQLでは同様の実装をしておりました。

Public Function ExcuteStoredProcedure(ByVal pstrStoredProcedureName As String, _
Optional ByRef pprm() As SqlParameter = Nothing) _
As Integer
Dim intLoopi As Integer
Dim intRet As Integer
Try
SqlCommand = New SqlCommand
SqlCommand.Transaction = SqlTransaction
SqlCommand.CommandType = CommandType.StoredProcedure
SqlCommand.CommandText = pstrStoredProcedureName
SqlCommand.CommandTimeout = mintTimeOut
SqlCommand.Connection = SqlConnection
If SqlCommand.Connection.State = ConnectionState.Closed Then
SqlCommand.Connection.Open()
End If
'パラメータ設定
If IsNothing(pprm) = False Then
SqlCommand.Parameters.Clear()
For intLoopi = 0 To pprm.Length - 1
SqlCommand.Parameters.Add(pprm(intLoopi).ParameterName, pprm(intLoopi).Value)
SqlCommand.Parameters.Item(pprm(intLoopi).ParameterName).Direction = pprm(intLoopi).Direction
SqlCommand.Parameters.Item(pprm(intLoopi).ParameterName).SqlDbType = pprm(intLoopi).SqlDbType
SqlCommand.Parameters.Item(pprm(intLoopi).ParameterName).Size = pprm(intLoopi).Size
SqlCommand.Parameters.Item(pprm(intLoopi).ParameterName).Value = pprm(intLoopi).Value
Next
End If
'ストアド実行
SqlCommand.ExecuteNonQuery()
'戻り値の取得
intRet = CInt(SqlCommand.Parameters.Item(0).Value)
Return intRet
Catch ex As Exception
Throw ex
Finally
For intLoopi = 0 To pprm.Length - 1
pprm(intLoopi) = SqlCommand.Parameters.Item(pprm(intLoopi).ParameterName)
Next
End Try
End Function

投稿者:はるぱぱ

編集 履歴 (0)
ウォッチ

この質問への回答やコメントをメールでお知らせします。