SQL Server ストアド作成テンプレート（例外処理、トランザクション内でセーブポイントを使用）

ストアド作成テンプレート.sql

USE [NPSDBIF]
GO

/****** Object:  StoredProcedure [dbo].[sp_test]    Script Date: 2021/05/31 21:31:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author     : 
-- Create date: 2021
-- Description: xx系IF処理
--              
--              ループ①
--               │        --
--               │
--               ├   ループ②
--               │     │ --
--               │     ├   ループ③
--               │     │　　--
-- =============================================

CREATE PROCEDURE [dbo].[sp_test]
             @argCount INT OUTPUT
            ,@argMessage NVARCHAR(4000) OUTPUT
AS
BEGIN
    DECLARE @Count       INT, 
            @Message     NVARCHAR(4000),
            @State       INT,--1 〜 127 ﾉ任意ﾉ整数
            @Id          VARCHAR(100),
            @Process     NVARCHAR(100),
            @ExitCode    INT,
            @VCExitCnt1  INT;

BEGIN TRY

    SET @Process='処理概要'
    
    DECLARE @TranCounter INT = @@TRANCOUNT
	-- ｶｰｿﾙ定義
    Declare Cur1 CURSOR LOCAL STATIC FOR 
        SELECT *
          FROM #UQ_XXX
    OPEN Cur1--ｶｰｿﾙｵｰﾌﾟﾝ
    FETCH NEXT FROM Cur1 INTO  @xcsd
    WHILE @@FETCH_STATUS = 0
    -- ========= ●①●【ﾙｰﾌﾟ】ﾙｰﾌﾟ内ﾉ実際ﾉ処理 ①▼▼▼===
    BEGIN
        IF @TranCounter > 0
            SAVE TRANSACTION ProcedureSave
        ELSE 
            BEGIN TRANSACTION

        BEGIN TRY
        
                -- ｶｰｿﾙ定義
                Declare Cur2 CURSOR LOCAL STATIC FOR
                    SELECT
                         *
                    FROM #TMP_1
                OPEN Cur2--ｶｰｿﾙｵｰﾌﾟﾝ
                FETCH NEXT FROM Cur2 INTO  @adfe
                WHILE @@FETCH_STATUS = 0
                -- ========= ●②●【ﾙｰﾌﾟ】ﾙｰﾌﾟ内ﾉ実際ﾉ処理 ②▼▼▼===
				BEGIN
					BEGIN TRY	
						
							--●③●【ﾙｰﾌﾟ】 ｶｰｿﾙ定義
							Declare Cur3 CURSOR LOCAL STATIC FOR
								SELECT
										*
								FROM #TMP_2;
							OPEN Cur3--ｶｰｿﾙｵｰﾌﾟﾝ
							FETCH NEXT FROM Cur3 INTO  @asfdasdfa
								WHILE @@FETCH_STATUS = 0
								BEGIN
									BEGIN TRY	
										--asdfasdfasf
                                    END TRY

									BEGIN CATCH
										SELECT @ExitCode = ERROR_NUMBER() ,
											   @Message  = ERROR_MESSAGE(),
											   @State    = ERROR_STATE();  
										-- ｴﾗｰ情報を保持
										
										-- ｶｰｿﾙ終了
										IF CURSOR_STATUS('local', 'Cur3') > 0
											BEGIN
												CLOSE Cur3;
												DEALLOCATE Cur3;
											END;
										-- 例外を外側の TRY～CATCH に投げる
										THROW;
									END CATCH
									-- ========= ●③●【ﾙｰﾌﾟ】ﾙｰﾌﾟ内ﾉ実際ﾉ処理 ③▲▲▲===
									--次ﾉ行ﾉﾃﾞｰﾀを取得ｼﾃ変数ﾍ値をｾｯﾄ
									FETCH NEXT FROM Cur3 INTO  @asfdasdfa
								END
							--●③●【ﾙｰﾌﾟ】ｶｰｿﾙ終了
							IF CURSOR_STATUS('local', 'Cur3') > 0
								BEGIN
									CLOSE Cur3;
									DEALLOCATE Cur3;
								END;
						END   
				
					END TRY

					BEGIN CATCH
						SELECT @ExitCode = ERROR_NUMBER() ,
							   @Message  = ERROR_MESSAGE(),
							   @State    = ERROR_STATE();  

						-- ｴﾗｰ情報を保持
						
						-- ｶｰｿﾙ終了
						IF CURSOR_STATUS('local', 'Cur2') > 0
							BEGIN
								CLOSE Cur2;
								DEALLOCATE Cur2;
							END;
						IF CURSOR_STATUS('local', 'Cur3') > 0
							BEGIN
								CLOSE Cur3;
								DEALLOCATE Cur3;
							END;
						-- 例外を外側の TRY～CATCH に投げる
						THROW;
					END CATCH

					FETCH NEXT FROM Cur2 INTO  @adfe
				END
				--●②●【明細ﾉﾙｰﾌﾟ】ｶｰｿﾙ終了
				IF CURSOR_STATUS('local', 'Cur2') > 0
					BEGIN
						CLOSE Cur2;
						DEALLOCATE Cur2;
					END;
				--一時TBLをｸﾘｱ
				
				-- ========= ●②●【明細ﾉﾙｰﾌﾟ】ﾙｰﾌﾟ内ﾉ実際ﾉ処理 ②▲▲▲===
                
            END
            -- ▲機能ﾌﾞﾛｯｸ END   : ●②●【明細ﾉﾙｰﾌﾟ】

            IF @TranCounter = 0  
				BEGIN
					COMMIT TRANSACTION
				END
                
        -- ▲▲▲▲▲▲▲▲▲▲ Main処理 END   ①▲▲▲▲▲▲▲▲▲▲
        END TRY

        BEGIN CATCH
            SELECT @ExitCode = ERROR_NUMBER() ,
                   @Message  = ERROR_MESSAGE(),
                   @State    = ERROR_STATE();                        
            
			PRINT 'DEBUG ERROR_MESSAGE()=' + convert(nvarchar(4000),ERROR_MESSAGE())

            IF @TranCounter = 0
				BEGIN
					IF XACT_STATE() <> 0
					BEGIN
						ROLLBACK TRANSACTION
					END
				END
            ELSE
				BEGIN
					IF XACT_STATE() <> -1  
					BEGIN
						ROLLBACK TRANSACTION ProcedureSave
					END
				END
            IF CURSOR_STATUS('local', 'Cur2') > 0
                BEGIN
                    CLOSE Cur2;
                    DEALLOCATE Cur2;
                END;
            IF CURSOR_STATUS('local', 'Cur3') > 0
                BEGIN
                    CLOSE Cur3;
                    DEALLOCATE Cur3;
                END;
           
            -- ｴﾗｰ情報を保持
            
        END CATCH
        --次の行のﾃﾞｰﾀを取得ｼﾃ変数ﾍ値をｾｯﾄ
        FETCH NEXT FROM Cur1 INTO  @xcsd
	END
	--●①●【ﾙｰﾌﾟ】 ｶｰｿﾙを閉ｼﾞﾙ
	IF CURSOR_STATUS('local', 'Cur1') > 0
		BEGIN
			CLOSE Cur1;
			DEALLOCATE Cur1;
		END;

    -- ========= ●①●ﾙｰﾌﾟ内ﾉ実際ﾉ処理 ①▲▲▲===

    --一時ものをクリア
   
    -- 後処理

    IF @CmnExecExitCode<> 0
        BEGIN
            SET @Process='連携後処理  >>>'
            SET @ExitCode = -1
        END

END TRY

BEGIN CATCH
    Select @ExitCode = ERROR_NUMBER() ,
           @Message  = ERROR_MESSAGE();
            
    --
	IF CURSOR_STATUS('local', 'Cur1') > 0
		BEGIN
			CLOSE Cur1;
			DEALLOCATE Cur1;
		END;
     IF CURSOR_STATUS('local', 'Cur2') > 0
        BEGIN
            CLOSE Cur2;
            DEALLOCATE Cur2;
        END;
    IF CURSOR_STATUS('local', 'Cur3') > 0
        BEGIN
            CLOSE Cur3;
            DEALLOCATE Cur3;
        END;
    --一時ものをクリア

END CATCH
    SET @argCount = @Count-- 件数
    SET @argMessage = @Message
    RETURN @ExitCode;
--- ﾌﾟﾛｼｰｼﾞｬﾉ終了
END

GO

テスト良い用例

--====================================================================
BEGIN TRAN
EXEC ストアド作成テンプレート 0 , 'UT-TEST: ストアド作成テンプレート.sql';
select 検証用列 from 検証用TBL
select 検証用列 from 検証用TBL
select 検証用列 from 検証用TBL

ROLLBACK TRAN
--====================================================================