SQL Server: Raise errors and warnings

Transact-SQL provides facilities to raise errors and warnings, as well as handling them. Here’s an introduction to this subject.

Raising errors and warnings

In SQL Server, we can generate an error or a warning with the RAISERROR statement. The most common usage looks like this:

RAISERROR (
    'Invalid user id',
    20, -- severity
    0   -- state
);

In this example, we’re raising a fatal error with severity 25 and state 0.

Severity must be in the range between 0 and 19. 0-18 represents a warning, 19-25 represents an error.

State must be in the range between 0 and 255. has no effect on SQL Server itself, but it’s useful for handling the error programmatically. If an error with the same message and severity can be raised in several places, the state can be used to find out where the error was raised.

Severity and state may be adjusted by SQL Server. If they’re less then 0 the value will be 0. If they exceed the maximum value, the maximum value will be used.

Errors and warnings

An error is a fatal error that terminates the current operation, and a warning is an anomaly that could indicate a problem or not, depending on the situation. Let’s see the differences, and how to generate them.

Errors

A mentioned, an error has a severity from 19 to 25.

An error must be written into the logs, so the WITH LOG option is required, as follows:

RAISERROR (...) WITH LOG;

An error is considered fatal, so it has some effects:

It rolls back the current transaction;
It terminates the execution of the current T-SQL program (a trigger, a procedure, etc).

Warnings

As mentioned, a warning must have a severity from 0 to 18. Warnings can only be handled by a TRY ... CATCH statement if their severity is bigger than 10.

The WITH LOG option discussed above is optional for warnings.

A warning does not rollback the transaction and does not terminate a program execution. We can, however, do one of these things or both:

ROLLBACK;
RAISERROR (...);
RETURN 1;

The order of the statements is important here:

ROLLBACK must precede RAISERROR, because if the warning is handled by a TRY ... CATCH block, anything after RAISERROR won’t be executed.
RETURN should follow both ROLLBACK and RAISERROR, or they won’t be executed.

RETURN always returns an integer value, 0 by default. This value can be used to indicate if the procedure succeeded or not, and why it failed. Conventionally, 0 indicates success and any other value indicates failure. We can use different non-zero returned values to indicate different problems: for example 1 for wrong arguments, 2 for missing rows, etc.

Other options

RAISERROR has a few more options that can occasionally be useful.

NOWAIT

Errors are normally sent to the client (and shown to the user) when the current program or T-SQL statement ends. To send the error to the client immediately, specify WITH NOWAIT:

RAISERROR (...) WITH NOWAIT;

SETERROR

You can also use SETERROR to set the error number to 50000, regardless of the severity level. 50000 is the code reserver to general user defined errors, by convention.

Reference

SQL Server: Handle errors