Why insert into statement is executed after divide by zero exception thrown

Question

I have the following query.

IF Object_id('tempdb..#test_tran') IS NOT NULL 
  DROP TABLE #test_tran 

CREATE TABLE #test_tran 
  ( 
     id   INT, 
     name VARCHAR(255) 
  ) 

GO

---------------------------------------- 

BEGIN TRAN 

  DECLARE @a FLOAT = 1 / 0.0 

  INSERT INTO #test_tran VALUES (1, 'Red') 

COMMIT TRAN 

---------------------------------------- 
GO 

SELECT * FROM   #test_tran

And output is

Msg 8134, Level 16, State 1, Line 17
Divide by zero error encountered.

(1 row affected)

(1 row affected)

Completion time: 2020-07-12T12:39:16.9097805+03:00

Why transaction was not stopped when error occurred at DECLARE @a FLOAT = 1 / 0.0 ? Why insert into statement is executed after divide by zero exception thrown?

If I use BEGIN TRY BEGIN CATCH the exception is caught and it prevents inserting, but I want to understand why execution is continue after exception thrown.

some errors abort the batch, others just abort the statement. See http://www.sommarskog.se/error_handling/SQL2000-I.html#actions for more on this. Use `xact_ abort` or try catch if you want the transaction rolled back on any error — Martin Smith, Jul 12 '20 at 09:51

score 2 · Accepted Answer · answered Jul 12 '20 at 10:01

SQL exceptions don't behave quite the same way as they do in application code. They don't always cause the stack to unwind up to a catch block (or process termination) - unless you actually have a catch block.

in SQL, some errors will cause a whole batch to terminate, others may not - depending on the setting of xact_abort. Some information on this can be found the the remarks of raiserror.

If the error has a severity greater than 10, and the error is not in a try block, then the client application will receive that error as part of an exception.

If the error as a severity greater than 20, the whole batch - and the connection - will terminate.

So, there are three ways to "detect" errors in TSQL:

Use a try/catch
Check the value of the @@error system variable after each statement.
If you always want to stop processing, then set xact_abort on

Why insert into statement is executed after divide by zero exception thrown

1 Answers1