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

  • 質問

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



    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
    




    2013年4月16日 3:43

回答

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

    SQL Serverには主要なトランザクションモードが3つあります。自動コミットトランザクション、明示的なトランザクション、暗黙のトランザクションの3つです。今回は明示的なトランザクションを開始していますから、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/

    • 回答の候補に設定 星 睦美 2013年4月19日 8:13
    • 回答としてマーク 星 睦美 2013年4月23日 4:18
    2013年4月17日 7:15

すべての返信

  • 書き忘れました。

    SQLServer2008R2を使用しています。

    2013年4月16日 3:53
  • ・usp_UpdateTest2ではトランザクションを設定していないが、usp_UpdateTestのトランザクションがusp_UpdateTest2にも適応されるのか。

    SQL Serverには主要なトランザクションモードが3つあります。自動コミットトランザクション、明示的なトランザクション、暗黙のトランザクションの3つです。今回は明示的なトランザクションを開始していますから、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/

    • 回答の候補に設定 星 睦美 2013年4月19日 8:13
    • 回答としてマーク 星 睦美 2013年4月23日 4:18
    2013年4月17日 7:15