The parts of an error
There are 6 standard error functions in SQL - each is described in more
detail below.
The Error Functions in SQL
The table below uses the following error message as an example:
This page uses this error message as its example.
Here's the 6 bits of information displayed for any error such as this:
|
Function |
What it returns |
Our example value |
|
ERROR_NUMBER() |
The internal error number. |
245 |
|
ERROR_SEVERITY() |
How serious the error is. |
16 |
|
ERROR_STATE() |
The system state. |
1 |
|
ERROR_PROCEDURE() |
The name of the procedure. |
spTest |
|
ERROR_LINE() |
The offending line number. |
10 |
|
ERROR_MESSAGE() |
A description of the error. |
Conversion failed ... |
You could use these to trap the above error and show it in a glossier form.
For example, suppose you ran the following query:
ALTER PROC spTest
AS
DECLARE @i int
BEGIN TRY
SET @i='owl'
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
END CATCH
This would produce the following output:
You can see the various bits of information about the error more clearly here.
Built-in error messages
If you want to see what error messages are installed with SQL Server, run the
following script:
SELECT * FROM sys.messages
On my instance of SQL Server 2008 R2 this gives 98,318 error messages, while
on SQL Server 2012 it gives 230,186. I'm not sure if this
means that the later version can generate more errors!
Here are the first few error messages for
2008 R2:
Where a % sign appears it means that a message's text can vary (as explained
in the final part of this blog).
Error message enthusiasts might like to use the system stored procedure
sp_AddMessage to add their own messages to those stored in the
Sys.Messages table.
The Error Number
On my instance of SQL Server the highest-numbered error is 49,902. When
you create your own errors, they are automatically flagged as number 50,000 by
default:
Here the web has gone down, so we raise an error to reflect this. Because we haven't given an error number, 50000 is assumed.
There's more detail on raising your own error messages at the end of this
blog series, including using the new THROW command.
The Error Severity
The severity number goes from 0 to 25. Here's what the numbers mean:
|
Band |
What this means |
|
0-10 |
These are regarded as warnings by SQL Server, and don't trigger error trapping (so, for example, they won't transfer control to the
CATCH clause of a TRY / CATCH block). |
|
11-18 |
Error message severity levels that you can use. |
|
19-25 |
These are severity levels reserved for error messages generated by members of the
sysadmin fixed server role! Any severity level of 20 or above is regarded as fatal by SQL Server. |
The Error State
There's not a great deal to say about this. It's provided as a way of
distinguishing between two messages with identical number. To all intents
and purposes it always takes the value 1, and is of no interest. If you
want to prove this to yourself, have a look at Microsoft's
take on the
subject.
The Error Procedure
This gives the name of the procedure or trigger in which the error occurred,
where there is one (otherwise, it returns Null). What
follows is
an example to illustrate where this might be useful.
First create a stored
procedure which takes an integer parameter, then tries to divide it by 0:
CREATE PROC spSub(@num int) AS
SELECT @num / 0
Now write a main procedure which calls this subprocedure, passing the number
1 to it:
CREATE PROC spMain
AS
BEGIN TRY
EXEC spSub 1
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
END CATCH
When you run this main procedure, it will crash. Here's what the
error-trapping will show:
The error occurred in the
spSub procedure, but is reported from spMain.
What this shows is that if an error isn't captured in a called procedure, it will
bubble up to the one which called it.
The Error Line Number
You can display line numbers for SQL in SQL Server by selecting Tools
--> Options, then ticking the box shown here:
Tick the
Line numbers box in the Transact-SQL
General tab.
You can then use SQL line numbers to see where an error occurred:
Here the error occurred on line number 7.
As mentioned earlier, however, by far the easiest way to find an error is just to
double-click on the offending line!
The Error Message
The text of error messages is the most useful part of an error, but there's
not much more to say about it here!
Now that we've covered the parts of an error message, let's have a look at
a practical example showing the use of error-handling in SQL.
