How to Detect an Error in T-SQL

After each statement in T-SQL, SQL Server sets the global variable @@error to 0, unless an error occurs, in which case @@error is set to the number of that error.

More precisely, if SQL Server emits a message with a severity of 11 or higher,
@@error will hold the number of that message.
And if SQL Server emits a message with a severity level of 10 or lower,
SQL Server does not set @@error,
and thus you cannot tell from T-SQL that the message was produced.


There is no way to prevent SQL Server from raising error messages.
There is a small set of conditions for which you can use SET commands to control whether these conditions are errors or not

@@error is set after each statement.

Example

CREATE TABLE notnull(a int NOT NULL)
DECLARE @err int,
       @value int
INSERT notnull VALUES (@value)
SELECT @err = @@error
IF @err <> 0
  PRINT '@err is ' + ltrim(str(@err)) + '.'

The output is:

Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'a', table
'tempdb.dbo.notnull'; column does not allow nulls. INSERT fails.
The statement has been terminated.
@err is 515.