Quick thought, you may want to re-think the error handling in the stored procedure, consider this code
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'dbo.TBL_SAMPLE_RESULT') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_RESULT;
CREATE TABLE dbo.TBL_SAMPLE_RESULT
(
SN_NUM INT NOT NULL CONSTRAINT PK_DBO_TBL_SAMPLE_RESULT_SN_NUM PRIMARY KEY CLUSTERED
,SN_RES INT NOT NULL
);
IF OBJECT_ID(N'dbo.TBL_SAMPLE_NUMBERS') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_NUMBERS;
CREATE TABLE dbo.TBL_SAMPLE_NUMBERS
(
SN_NUM INT NOT NULL CONSTRAINT PK_DBO_TBL_SAMPLE_NUMBERS_SN_NUM PRIMARY KEY CLUSTERED
);
INSERT INTO dbo.TBL_SAMPLE_NUMBERS (SN_NUM)
SELECT TOP(10)
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
)
FROM sys.all_columns SAC;
GO
IF OBJECT_ID(N'dbo.USP_ERROR_ON_SIX') IS NOT NULL DROP PROCEDURE dbo.USP_ERROR_ON_SIX;
GO
CREATE PROCEDURE dbo.USP_ERROR_ON_SIX
(
@INPUT_NO INT
)
AS
SELECT
@INPUT_NO AS FROM_NUM
,@INPUT_NO / (@INPUT_NO % 6) AS TO_NUM
GO
IF OBJECT_ID(N'dbo.USP_RUN_CURSOR_NO_ERROR_HANDLING') IS NOT NULL DROP PROCEDURE dbo.USP_RUN_CURSOR_NO_ERROR_HANDLING;
GO
CREATE PROCEDURE dbo.USP_RUN_CURSOR_NO_ERROR_HANDLING
AS
/* This procedure will NOT stop the execution on error */
DECLARE @IN_NUM INT = 0;
DECLARE R_SET CURSOR FAST_FORWARD
FOR
SELECT
TSN.SN_NUM
FROM dbo.TBL_SAMPLE_NUMBERS TSN
ORDER BY TSN.SN_NUM;
OPEN R_SET
FETCH NEXT FROM R_SET INTO @IN_NUM;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
INSERT INTO dbo.TBL_SAMPLE_RESULT (SN_NUM, SN_RES)
EXEC dbo.USP_ERROR_ON_SIX @IN_NUM
COMMIT TRAN
FETCH NEXT FROM R_SET INTO @IN_NUM;
END
CLOSE R_SET
DEALLOCATE R_SET
GO
IF OBJECT_ID(N'dbo.USP_RUN_CURSOR_WITH_ERROR_HANDLING') IS NOT NULL DROP PROCEDURE dbo.USP_RUN_CURSOR_WITH_ERROR_HANDLING;
GO
CREATE PROCEDURE dbo.USP_RUN_CURSOR_WITH_ERROR_HANDLING
AS
/* This procedure will stop the execution on error */
DECLARE @IN_NUM INT = 0;
DECLARE R_SET CURSOR FAST_FORWARD
FOR
SELECT
TSN.SN_NUM
FROM dbo.TBL_SAMPLE_NUMBERS TSN
ORDER BY TSN.SN_NUM;
OPEN R_SET
FETCH NEXT FROM R_SET INTO @IN_NUM;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
BEGIN TRAN
INSERT INTO dbo.TBL_SAMPLE_RESULT (SN_NUM, SN_RES)
EXEC dbo.USP_ERROR_ON_SIX @IN_NUM
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
BREAK;
END CATCH
FETCH NEXT FROM R_SET INTO @IN_NUM;
END
CLOSE R_SET
DEALLOCATE R_SET
GO
/* Run the procedure without error handling
which will skip one row
*/
EXEC dbo.USP_RUN_CURSOR_NO_ERROR_HANDLING;
GO
SELECT
TSR.SN_NUM
,TSR.SN_RES
FROM dbo.TBL_SAMPLE_RESULT TSR;
GO
TRUNCATE TABLE dbo.TBL_SAMPLE_RESULT;
GO
/*
This one will stop at the error
*/
EXEC dbo.USP_RUN_CURSOR_WITH_ERROR_HANDLING;
GO
SELECT
TSR.SN_NUM
,TSR.SN_RES
FROM dbo.TBL_SAMPLE_RESULT TSR;
GO
TRUNCATE TABLE dbo.TBL_SAMPLE_RESULT;
GO
/* This will fully rollback all inserts */
BEGIN TRY
BEGIN TRAN
EXEC dbo.USP_RUN_CURSOR_NO_ERROR_HANDLING;
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
END CATCH
GO
SELECT
TSR.SN_NUM
,TSR.SN_RES
FROM dbo.TBL_SAMPLE_RESULT TSR;
GO
TRUNCATE TABLE dbo.TBL_SAMPLE_RESULT;
GO
/* This will also fully rollback all inserts */
BEGIN TRY
BEGIN TRAN
EXEC dbo.USP_RUN_CURSOR_WITH_ERROR_HANDLING;
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
END CATCH
GO
SELECT
TSR.SN_NUM
,TSR.SN_RES
FROM dbo.TBL_SAMPLE_RESULT TSR;
GO
No error handling results
SN_NUM SN_RES
----------- -----------
1 1
2 1
3 1
4 1
5 1
7 7
8 4
9 3
10 2
Results with error handling
SN_NUM SN_RES
----------- -----------
1 1
2 1
3 1
4 1
5 1
Results for outer (no error handling) procedure in a try catch
SN_NUM SN_RES
----------- -----------
Results for outer (with error handling) procedure in a try catch
SN_NUM SN_RES
----------- -----------
Edit: added missing code