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.
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).
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.
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:
RAISERROR, because if the warning is handled by a
TRY ... CATCHblock, anything after
RAISERRORwon’t be executed.
RETURNshould follow both
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.
RAISERROR has a few more options that can occasionally be useful.
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
RAISERROR (...) WITH NOWAIT;
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.