Why does T-SQL not stop on errors

Question

If I do the following query

EXEC spFoo
PRINT 'TEST'

and spFoo throws an error, it still executes the print statement.

However if I do

BEGIN TRY
    EXEC cdb.spFoo
    PRINT 'TEST'
END TRY
BEGIN CATCH
    THROW;
END CATCH

It behaves as I expected and does not continue after an error.

Could someone please explain this behaviour for me? It still continues on even if I encapsulate it in a transaction. It is not just the with a print-statement but also with any other thing. My initial thought was that it was a severity problem but the severity was level 16. Is this normal T-SQL behaviour? If so, what motivates this design that contradicts every other language I have ever worked with that directly escalates the error?

I have tried and seen the same behaviour in SQL Server 2012, 2014 and 2017 across several different machines. The stored procedure in question is linked to a SQL CLR.

SQL Server error mechanics aren't equal to object oriented programming languages, the error won't (always) bubble up until a catch finally handles it. For example, if the error severity is 20 or higher the session will be instantly terminated, even with a CATCH. You should try reading about transactions (and xact_abort) and common error severity levels (foreign key fails, tempdb full fail, etc.). Each error might behave differently unfortunately. — EzLo, May 16 '18 at 08:08
So, you've never encountered, say, Visual Basic's infamous [On Error Resume Next](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/on-error-statement) or any C-style language with an API with out-of-band error signalling (`GetLastError`-style). Your unfamiliarity with how some older languages dealt with errors doesn't make T-SQL unique in this regard. — Damien_The_Unbeliever, May 16 '18 at 08:13
Possible duplicate of [What is the best practice use of SQL Server T-SQL error handling?](https://stackoverflow.com/questions/725891/what-is-the-best-practice-use-of-sql-server-t-sql-error-handling) pretty old question, but the link provided in the answer is very detailed: http://www.sommarskog.se/error-handling-I.html — Tanner, May 16 '18 at 08:19
A more up to date link provided in the comments of the above answer: http://www.sommarskog.se/error_handling/Part1.html — Tanner, May 16 '18 at 08:25
and also: http://www.sommarskog.se/error_handling/Part2.html — Tanner, May 16 '18 at 08:46

score 3 · Answer 1 · answered May 16 '18 at 08:33

Severity was level 16 is a warning-level. The user is required to handle any errors - including defining when termination is required.

With your first example:

EXEC spFoo
PRINT 'TEST'

these are independent statements and, although spFoo may fail, the server will move onto the next statement. This is because severity is less than 20, the batch has not automatically been terminated.

With your second example,

BEGIN TRY
    EXEC cdb.spFoo
    PRINT 'TEST'
END TRY
BEGIN CATCH
    THROW;
END CATCH

you have taken ownership of deciding what is associated with what.

Since one item in the TRY block failed, it would not move onto the next.

THROW always terminates a batch.

Once you called THROW, if you have any code that continues afterwards, it will not be carried out. If that's important, you can use RAISERROR to continue.

A detailed explanation of errors

Part 2 of explanations

An answer from the same person

Severity levels

score 1 · Accepted Answer · answered Apr 11 '22 at 07:47

One would think error handling was straightforward. Not really. I have taken a screenshot from Erland Sommarskog's excellent article, which shows how different errors behave:

Seeing that PRINT 'TEST' is being ran after the failed exec, I guess you are in the Name=Statement-terminating, XACT_ABORT OFF, TRY-CATCH OFF cell which indicates "Aborts statement", which means the next statement still runs. Catching works and skips the next step.

par · Answer 3 · 2018-05-16T08:12:21.100

0

You should explanation given here for TRY...CATCH

A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.

In SQL, TRY..CATCH works very differently than C#/VB etc. so based on severity it works.

If you need to see if any error occurred in previous statement, use @@ERROR.

Example, IF @@ERROR <> 0 PRINT 'TEST'

Try printing what result @@ERROR giving. It may give you any hint.

Returns 0 if the previous Transact-SQL statement encountered no errors.

(From above link) @@ERROR Returns an error number if the previous statement encountered an error. If the error was one of the errors in the sys.messages catalog view, then @@ERROR contains the value from the sys.messages.message_id column for that error. You can view the text associated with an @@ERROR error number in sys.messages.

Check if any helpful information found from @@ERROR

edited May 16 '18 at 08:12

answered May 16 '18 at 08:04

par

1,061
1
11
29

1

this don't seems like answer to the question. – Aswani Madhavan May 16 '18 at 08:05
@AswaniMadhavan, It depends. It is not going in Catch because of low severity and continues execution. – par May 16 '18 at 08:06
@AswaniMadhavan, I guess i will need to delete this answer :) – par May 16 '18 at 08:13

score 0 · Answer 4 · answered May 16 '18 at 08:13

TRY.. CATCH

Will not stop the process of the query. If you do not want to continue after catching the error, then please use RETURN. This will stop the process. Also if you are using BEGIN TRANSACTION, please use ROLLBACK before returning from the process. Otherwise it will end up in uncommitted transactions.

Why does T-SQL not stop on errors

4 Answers4