- PR -

SQLServer2000 ストアドのエラー処理

1
投稿者投稿内容
はるぱぱ
会議室デビュー日: 2006/08/27
投稿数: 3
投稿日時: 2006-08-27 14:15
ストアドプロシージャの実行中に致命的でないエラー
（一意制約違反等）が発生した場合、
戻り値が戻らずに、サーバエラーが出力されます。

ソースは以下のようになっております。
--------------------------------------------------
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"以下を実行しております。
サーバエラーを出力させずに、
戻り値を正しく戻すにはどのようにすればよろしいのでしょうか？

お知恵を拝借できればと存じます。
sou
ベテラン
会議室デビュー日: 2002/09/25
投稿数: 56
投稿日時: 2006-08-28 00:27
ストアドプロシージャ内でエラーが発生した場合に、
エラーを出力させずに処理してしまう実装はどうかと思いますが・・。
ましてや、一意制約違反などは、私にとっては重大なエラーだと思うんですが・・。

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

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

また、どうしてもサーバーから返されるエラーを無視したければ、
try-catchで例外を処理してあげればいいと思います。
未記入
ぬし
会議室デビュー日: 2004/09/17
投稿数: 667
投稿日時: 2006-08-28 09:49
引用:
エラーが起こらないように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 をチェックするようなコーディングはできなくなります。
はるぱぱ
会議室デビュー日: 2006/08/27
投稿数: 3
投稿日時: 2006-08-28 10:28
SUOさん　未記入さん
回答ありがとうございます。

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

ただ、なぜかVBで例外エラーをトラップすると、
OUTのパラメータは正しく格納されているのようなので対応はできるのですが、
書籍やMSDNのサンプルを調べ、
上記のようなプログラムが基本的なエラー処理と理解しておりましたので、
どうも腑に落ちないのです。
SET XACT_ABORT ONの逆のように、
エラーを無効にする設定があるのかと思い質問させて頂きました。
THREE-ONE
常連さん
会議室デビュー日: 2006/08/17
投稿数: 36
投稿日時: 2006-08-28 14:04
こんにちは

そもそも、サーバーエラーとはどんなエラーが出ているのでしょうか？
戻り値は正しく返っているけど、受け取り側でなんらかを行って、エラーになっている可能性は無いのでしょうか？
受け取り側のコードも（可能な範囲で）公開されるとよいかもしれません。
はるぱぱ
会議室デビュー日: 2006/08/27
投稿数: 3
投稿日時: 2006-08-28 17:54
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
1

スキルアップ／キャリアアップ（JOB@IT）