ストアドの入れ子について

Question

お世話になっております。

usp_UpdateTestでは下記を行っています。

・t_testへデータを追加

・usp_UpdateTest2を呼ぶ

・エラーの場合は、エラー番号を返す

私の質問は下記の通りです。

・usp_UpdateTest2ではトランザクションを設定していないが、usp_UpdateTestのトランザクションがusp_UpdateTest2にも適応されるのか。

・usp_UpdateTest2でエラーが発生した場合、@returnCode、@errorNoはNullとなる。@returnCode、@errorNoを取得したい場合はどうすればよいのか。

・この方法が果たして正しいのか。

宜しくお願い致します。

CREATE PROCEDURE usp_UpdateTest
     @value1     INT = Null
    ,@value2     INT = Null
    ,@returnCode INT OUTPUT
    ,@errorNo    INT OUTPUT	
AS

BEGIN
    SET NOCOUNT ON;
	
    SET @returnCode = 0;
	
    BEGIN TRY
        BEGIN TRANSACTION
            INSERT INTO t_test 
	         (value1)
               VALUES
                 (@value1);

            EXEC usp_UpdateTest2 @value2;

            SET @returnCode = 1;

        COMMIT TRANSACTION;
    END TRY
	
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @errorNo = ERROR_NUMBER();
		
        EXEC usp_RethrowError; 
    END CATCH

END
GO

CREATE PROCEDURE usp_UpdateTest2
    @value2 INT = Null
AS

BEGIN
    SET NOCOUNT ON;
	
    UPDATE t_test2
       SET value2 = @value2;
		
END
GO

下記はhttp://blogs.wankuma.com/trapemiya/archive/2008/03/13/127556.aspxをそのまま使用しました。

CREATE PROCEDURE usp_RethrowError
AS
BEGIN
    SET NOCOUNT ON;

    -- Return if there is no error information to retrieve.
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE 
        @errorMessage    NVARCHAR(4000)
    ,   @errorNumber     INT
    ,   @errorSeverity   INT
    ,   @errorState      INT
    ,   @errorLine       INT
    ,   @errorProcedure  NVARCHAR(200);

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT 
        @errorNumber    = ERROR_NUMBER()
    ,   @errorSeverity  = ERROR_SEVERITY()
    ,   @errorState     = ERROR_STATE()
    ,   @errorLine      = ERROR_LINE()
    ,   @errorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    -- Building the message string that will contain original
    -- error information.
    SELECT @ErrorMessage = 
		N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: '+ ERROR_MESSAGE();

	-- Raise an error: msg_str parameter of RAISERROR will contain
	-- the original error information.
    RAISERROR 
        (
         @errorMessage, 
         @errorSeverity, 
         1,               
         @errorNumber,    -- parameter: original error number.
         @errorSeverity,  -- parameter: original error severity.
         @errorState,     -- parameter: original error state.
         @errorProcedure, -- parameter: original error procedure name.
         @errorLine       -- parameter: original error line number.
        );

END
GO

Answer 1

書き忘れました。

SQLServer2008R2を使用しています。

Answer 2

・usp_UpdateTest2ではトランザクションを設定していないが、usp_UpdateTestのトランザクションがusp_UpdateTest2にも適応されるのか。

SQL Serverには主要なトランザクションモードが３つあります。自動コミットトランザクション、明示的なトランザクション、暗黙のトランザクションの３つです。今回は明示的なトランザクションを開始していますから、usp_UpdateTest2内で実行されるSQLもそのトランザクションに入ると思います。（明確に書いてある文献を見つけることはできませんでしたが、トランザクションのモードの考え方から言って間違いないと思います。実際にテストをして検証も行いました。）

（参考）
トランザクション、ロッキング、同時実行性
http://msdn.microsoft.com/ja-jp/library/cc341866

・usp_UpdateTest2でエラーが発生した場合、@returnCode、@errorNoはNullとなる。@returnCode、@errorNoを取得したい場合はどうすればよいのか。

私がテストした限りではNullとならず、usp_UpdateTest2で発生したErrorNumberが取れ、@errorNoにセットされていました。EXEC usp_RethrowError; で例外を再スローしているので、それで取れていないように見えているのかもしれません。

・この方法が果たして正しいのか。

ベストな方法かは別として、特に誤りではないと思います。

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/