Exception Handling not handling Error in SQL-SERVER

Question

We are handling Exceptions in our stored procedures using Try/Catch block. In a stored procedure I have intentionally made an error to check how the exceptions being handled. I wrote a stored procedure like as below,

CREATE PROCEDURE TEST
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
    BEGIN TRAN
        
              ;WITH CTE
              AS
              (SELECT TOP 10 * 
               FROM student)

               SELECT * FROM CTE

               SELECT * INTO #VAL
               FROM CTE

   
      IF @@ERROR = 0
              BEGIN
                  COMMIT TRAN;
              END
      END TRY
      BEGIN CATCH
              SELECT @@ERROR AS ERROR, ERROR_LINE() AS [Error Line], ERROR_MESSAGE() AS [Error Message] 
      ROLLBACK TRAN;
      END CATCH
SET NOCOUNT OFF;
END

In the Above Stored procedure I have used a CTE multiple Times. I expected that SQL-SERVER will handle the exception but it didn't. While Executing the Above stored Procedure I got below error

Msg 208, Level 16, State 1, Procedure TEST, Line 16

Invalid object name 'CTE'.

Msg 266, Level 16, State 2, Procedure TEST, Line 16

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

Why the Exception is not handled ? Could someone give idea on this?

Thanks for the help.

TheGameiswar · Accepted Answer · 2016-01-07 13:26:55Z

2

stored procedures follow deferred object name resolution while executing.So in this case cte is a non existent object to it .

Further try catch cannot handle this type of errors

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

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

Please see MSDN for more info(see errors unaffected by try catch )

answered Jan 7, 2016 at 13:26

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bmsqldev Over a year ago

Thanks for teaching me and the msdn link was really helpful :)

Collectives™ on Stack Overflow

Exception Handling not handling Error in SQL-SERVER

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related