1

Please find the below sample code

    drop table  if exists #myTest
    create table #myTest (
        id int,
        name varchar(30)
    )    
    
    insert into #myTest values
        (1, 'John'),
        (2, 'Somu')
    
    --select 1/0                  -- Query 1 throws error
    --select cast('ABC' as int)   -- Query 2 throws error
    
    insert into #myTest values
        (3, 'Bela'),
        (2, 'Steve')
    
    select * from #myTest

When I uncomment 'Query 1' it throws below error but successfully inserts all 4 rows.

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

When I uncomment 'Query 2' it throws below error but this time inserts no rows.

Msg 245, Level 16, State 1, Line 63 Conversion failed when converting the varchar value 'ABC' to data type int.

Looking at the error message it is not clear why one error allowed insertion of rows but second one did not. Could someone please explain what is the difference between the two?

Dale K
  • 25,246
  • 15
  • 42
  • 71
KnowledgeSeeeker
  • 620
  • 1
  • 9
  • 14
  • 6
    This is the default behaivour with `xact_abort` off; not every error in SQL Server is a serverity high enough to abort the batch execution. If you try with `set xact_abort on` (which is always a good idea) you'll find both errors behave the same. – Stu Jul 07 '23 at 19:08
  • 4
    Sql Server is notoriously uneven when it comes to error handling. Set XACT ABORT ON, transactions and TRY CATCH is usually the most sane way of handling it. – siggemannen Jul 07 '23 at 19:46
  • 8
    Read this for the gory details: https://sommarskog.se/error_handling/Part1.html – David Browne - Microsoft Jul 07 '23 at 19:56
  • 1
    @David Browne couldn't agree more. My favorite part is the table in https://sommarskog.se/error_handling/Part2.html#classification – George Menoutis Jul 08 '23 at 14:30
  • Doing `SET XACT_ABORT ON` at the top of your script makes everything just a little bit more predictable. – Charlieface Jul 09 '23 at 01:30

1 Answers1

0

There are two primary classes of errors in SQL Server: batch-aborting and statement-aborting (there are also other classes, but they are not usually relevant).

Statement-aborting errors behave rather oddly, as you have observed: they terminate the entire statement, but continue to the next line, as long as there is no BEGIN CATCH block. Effectively, every such line behaves like this

SAVE TRAN save1;

BEGIN TRY
    -- do the statement here
END TRY
BEGIN CATCH
    ROLLBACK TRAN save1;
END CATCH;

Batch-aborting errors are much more obvious, they simply abort the whole batch, rolling back the transaction at the same time.

Having said that, you are highly advised to always run with SET XACT_ABORT ON; because that upgrades statement-aborting errors to be batch-aborting, and generally making error-handling much more sane.

You can set it ON for all connections using

DECLARE @opts int;
SELECT @opts = CAST(value AS int) | 16384  -- bitwise OR the value for XACT_ABORT
FROM sys.configurations
WHERE name = 'user options'

EXEC sp_configure N'user options', @opts;
GO
RECONFIGURE

See also this series of articles for many more details on SQL Server's idiosyncratic error-handling.

Charlieface
  • 52,284
  • 6
  • 19
  • 43