WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 519 reviews for our classroom and online training
How error messages work in SQL (including TRY, CATCH, RAISERROR and THROW)
Part two of a four-part series of blogs

You can use BEGIN TRY to trap errors from SQL Server 2005 onwards, and also raise your own errors using RAISERROR or THROW - this blog explains all!

  1. Error-handling and error messages
  2. The parts of an error (this blog)
  3. A practical example of error-handling
  4. Raising Errors

If you want to learn more about SQL in a classroom setting, you could consider attending a Wise Owl course; otherwise, see the rest of this comprehensive SQL tutorial.

Posted by Andy Brown on 03 May 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

Typical error message

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:

-- a stored procedure which will fail




-- create an integer variable

DECLARE @i int




-- try to store text in it

SET @i='owl'






-- display details of error


ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,




This would produce the following output:

The results of this query

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:

-- display all built-in errors

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:

The first few error messages

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:

Raising an error

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


-- divide 1 by 0

SELECT @num / 0

Now write a main procedure which calls this subprocedure, passing the number 1 to it:





-- try to execute the subprocedure

EXEC spSub 1






-- display details of error


ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,




When you run this main procedure, it will crash.  Here's what the error-trapping will show:

The error trap messages

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:

Line numbers box

Tick the Line numbers box in the Transact-SQL General tab.


You can then use SQL line numbers to see where an error occurred:

The line number being used

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.

This blog has 0 threads Add post