Errors are not handled by Try/Catch Block in SQL Server

Question

We are using following error handling pattern in SQL Server stored procedures:

ALTER PROCEDURE [dbo].[USP_Districtdata_Import]
    @DistrictData DistrictData Readonly
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
    BEGIN TRAN

    --Insert the new records into BudgetDistrict Table.
    INSERT INTO [dbo].[BudgetDistrict]
    (
     DistrictID,
     [Year],
     Season,
     BudgetStateID,
     ProjectedReturnCountIsCalc,
     RowStatus,
     CreatedBy,
     CreatedDate,
     LastModifiedBy,
     LastModifiedDate,
     EnableBudgetLock
     )

    SELECT
    DISTINCT list.[District Id],list.[Year],list.[Season],1,0,'A',@CreatedBy,@Updtime,@CreatedBy,@Updtime,0
    FROM @DistrictData liston]
        AND bud.RowStatus = 'A'
        )
    LEFT OUTER JOIN [dbo].[BudgetDistrict] bud
      ON (bud.DistrictID  = list.[District Id]
        AND bud.[Year]    = list.[Year]
        AND bud.[Season]  = list.[Seas
    WHERE bud.DistrictID IS NULL


    --Update the existing pending budgets
    UPDATE wk
    SET  wk.Budget         = list.[Budget], 
         wk.BudgetAdjusted = list.[Budget],
         wk.ProjectedReturnCount = list.[ProjectedReturn Count], 
         wk.CreatedBy      = @CreatedBy, 
         wk.CreatedDate    = @Updtime,
         wk.LastModifiedBy = @CreatedBy, 
         wk.LastModifiedDate = @Updtime  
    FROM @DistrictData list
    INNER JOIN [dbo].[BudgetDistrict] bud
      ON (bud.DistrictID  = list.[District Id]
        AND bud.[Year]    = list.[Year]
        AND bud.[Season]  = list.[Season])
    INNER JOIN [dbo].[BudgetDistrictWeekly] wk
      ON (wk.NationalBudgetID = bud.BudgetDistrictID
       AND wk.[WeekDate]      = list.[Week])
    WHERE bud.RowStatus  = 'A'
       AND wk.RowStatus  = 'A'
       AND bud.BudgetStateID = 1

    --Insert the new budgets
    INSERT INTO [dbo].[BudgetDistrictWeekly]
    (
    WeekDate,
    Budget,
    BudgetAdjusted,
    RowStatus,
    CreatedBy,
    CreatedDate,
    LastModifiedBy,
    LastModifiedDate,
    ProjectedReturnCount
    )

    SELECT LIST.[Week],list.[Budget],list.[Budget],'A',@CreatedBy,@Updtime,@CreatedBy,@Updtime,[ProjectedReturn Count]
    FROM @DistrictData list
    LEFT JOIN [dbo].[BudgetDistrict] bud
      ON (bud.DistrictID  = list.[District Id]
        AND bud.[Year]    = list.[year]
        AND bud.[Season]  = list.Season
        AND bud.RowStatus = 'A')
    WHERE bud.DistrictID IS NULL

       IF @@ERROR = 0
       BEGIN
              COMMIT TRAN;
      END
    END TRY
    BEGIN CATCH
        SELECT 
            ERROR_NUMBER() AS ErrorNumber,  
            ERROR_LINE() AS ErrorLine, 
            ERROR_MESSAGE() AS ErrorMessage;
        ROLLBACK TRAN;
    END CATCH

SET NOCOUNT OFF;
END

but when the below error occurs in the stored procedure the try/catch block didn't work.

Error details: stored Procedure tried to insert a NULL value into a not null column.

During the execution of the stored procedure, I got following error

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

Msg 3903, Level 16, State 1, Line 30
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Why is the exception not handled? Please help

Not an answer but the `IF @@ERROR = 0` is redundant. if Sql server does raise an error, the flow should go directly to the catch block. — Zohar Peled, May 11 '16 at 08:07
means we don't need to use @@ERROR = 0 in the try/catch block? — bmsqldev, May 11 '16 at 08:13
Perhaps there is an inner transaction somewhere in your statements? can you show the code that's creating this problem? — Zohar Peled, May 11 '16 at 08:17
no other inner transactions. there are two insert statements and one update statement. i will edit my question — bmsqldev, May 11 '16 at 08:32
Some errors are not caught by `CATCH` statement, such as syntax errors e.g. See the [documentation](https://msdn.microsoft.com/en-us/library/ms175976.aspx) for more details (_Errors Unaffected by a TRY…CATCH Construct_ section). — i-one, May 11 '16 at 08:35
i read it. but as per the documentation errors with severity 20 and above will not handled. but this error has severity 16 only — bmsqldev, May 11 '16 at 08:48
try catch is for handling execution errors not syntax errors — vignesh, Jul 23 '20 at 18:11

score 3 · Accepted Answer · answered May 11 '16 at 09:00

3

According to documentation (Errors Unaffected by a TRY…CATCH Construct section) some errors are not caught by CATCH statement.

Particularly:

Compile errors, such as syntax errors, that prevent a batch from running.
Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

Quite typical situation is when a stored proc accesses table column (or accesses object) that were removed after the stored proc has been created.

See the sample below:

create table #test (id int, somecolumn uniqueidentifier)
GO
create procedure #testSP
as
begin try
    set nocount on;

    begin tran;

    insert into #test (id, somecolumn)
    values (1, 0x);

    commit;

end try
begin catch
    rollback;
    print 'Error happened';
end catch
GO
exec #testSP
GO
alter table #test
    drop column somecolumn
GO
exec #testSP
GO

The above code produces

Msg 207, Level 16, State 1, Procedure #testSP_..._00053EAF, Line 33 Invalid column name 'somecolumn'.

Msg 266, Level 16, State 2, Procedure #testSP_..._00053EAF, Line 33 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.

As you see despite the severity level is 16, the errors are not caught and message Error happened does not printed.

answered May 11 '16 at 09:00

i-one

5,050
1
28
40

thanks for the explanation. so do you mean "Insert null value into the not null column " also fall under this category? – bmsqldev May 11 '16 at 09:04
@bmsqldev, no. But `ERROR_MESSAGE()` _Returns the message text of the error that caused the CATCH block of a TRY…CATCH construct to be run._ Probably there may be something that happened _earlier_ in the call stack. – i-one May 11 '16 at 09:13
might be. but i wonder why it is shown as a actual error too. the sproc should throw error number and error message if the above try/catch block works – bmsqldev May 11 '16 at 09:18
@bmsqldev, try to reproduce situation, running your code out of the stored proc (and probably out of the try-catch too), executing the same statements one by one, I think it should help to clarify what really happens and find the point, where things goes wrong. – i-one May 11 '16 at 09:34
when i executed the code out of the sproc try/catch block catches the error correctly. i used the same structure with adhoc sql statements. – bmsqldev May 11 '16 at 10:29
I figured out a way to way to handle data consistency in this situation from this link https://www.mssqltips.com/sqlservertip/4018/sql-server-transaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-commit-statements/ – bmsqldev May 11 '16 at 10:49
@bmsqldev, good if you found the solution, however for me it is not clear still what happens. May be `catch` actually work properly in your situation, but transaction is already rolled back when `catch` block is reached? Then in the `catch` block `rollback` is called again, causing transaction count mismatch. It doesn't seem that inserting a NULL value into a not null column causes rollback automatically. May be some triggers on target tables doing `rollback` on some conditions? – i-one May 11 '16 at 11:12
i am not certain about whether rollback happens when catch block is reached. this is the standard format we are following. please guide me if there are any problems in try/catch syntax i use. Also I would add one more thing, actually I see the error is caught in the result set tab along with these system errors in message tab. – bmsqldev May 12 '16 at 05:31
@bmsqldev, inside `catch` you are doing `rollback` unconditionally. If transaction is rolled back already, this will cause _"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."_ error. I think it is better to use `if @@trancount > 0 rollback` (in the link you provided this pattern is used also). – i-one May 12 '16 at 05:41
thanks for the suggestion. I finally figured out why the error messages are thrown. i actually executes the stored procedure within begin tran rollback tran block .....that causes the error. when i execute it outside the block , it catches exception correctly – bmsqldev May 12 '16 at 06:21

score 1 · Answer 2 · answered Mar 24 '20 at 20:44

1

there is a way, if you are able to use dynamic sql, so try catch works

answered Mar 24 '20 at 20:44

Ali CAKIL

383
5
21

Errors are not handled by Try/Catch Block in SQL Server

2 Answers2