To totally unlock this section you need to Log-in
Login
You can use BEGIN TRY to trap errors from SQL Server 2005 onwards, and also raise your own errors using RAISERROR or THROW.
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:
Here's the 6 bits of information displayed for any error such as this:
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
ALTER PROC spTest
AS
-- create an integer variable
DECLARE @i int
BEGIN TRY
-- try to store text in it
SET @i='owl'
END TRY
BEGIN CATCH
-- display details of error
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:
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 our example instance of SQL Server 2008 R2 this gives 98,318 error messages, while on SQL Server 2012 it gives 230,186.
Here are the first few error messages for 2008 R2:
NOTE: 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 our 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.
The Error Severity
The severity number goes from 0 to 25. Here's what the numbers mean:
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.
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:
CREATE PROC spMain AS
BEGIN TRY
-- try to execute the subprocedure
EXEC spSub 1
END TRY
BEGIN CATCH
-- display details of error
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 Line Number
You can display line numbers for SQL in SQL Server by selecting Tools --> Options, then ticking the box shown here:
You can then use SQL line numbers to see where an error occurred:
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.
A practical example of error-handling
Let's begin filling some data on two tables on the example database:
What our stored procedure should do
Suppose that we want to create a stored procedure to allow us to add a book easily. We could use this to add books for existing authors:
-- author number 3 does exist, so this should work
spAddBook 'The L-Shaped Room', 3
Alternatively, we might try to add books whose authors don't yet exist:
-- there is no author number 4
spAddBook 'The Pelican Brief', 4
Here's what we should see for these two cases:
Building the stored procedure
The start of the stored procedure should specify which parameters it takes:
-- procedure to insert a book
ALTER PROC spAddBook(
@BookName varchar(100),
@AuthorId int
)
AS
The procedure should then try inserting the book in question, and displaying the record just added if this all goes OK:
BEGIN TRY
-- try inserting book
INSERT INTO tblBook(
BookName,
AuthorId
) VALUES (
@BookName,
@AuthorId
)
-- show last inserted book if worked
SELECT TOP 1 * FROM tblBook
ORDER BY BookId DESC
END TRY
If this fails (perhaps because the author number given doesn't exist in the authors table, so that the referential integrity constraint prevents insertion of the book), control will jump to the CATCH clause:
BEGIN CATCH
-- if couldn't insert book, show why
SELECT
'Could not do' AS ErrorMessage,
ERROR_NUMBER() AS 'Error no',
'Author ' + CAST(@AuthorId AS varchar(10)) + ' not found' AS Problem
END CATCH
Running the stored procedure
Here are two calls to this stored procedure (the first will insert a row, the second will display the error details):
-- add a book by Lynne Reid Banks
spAddBook 'The L-Shaped Room', 3
-- there is no author 5 yet!
spAddBook 'The Lacuna', 5
Raising Errors
If the built-in SQL errors aren't annoying enough, you could always add your own.
Two different ways to raise errors - which is better?
There are two ways to generate errors in T-SQL - using RAISERROR or THROW. Here's a basic example of each:
-- raise error of severity level 15
RAISERROR('This went pear-shaped',15,1);
-- alternatively, throw the same error message
THROW 50000, 'This went pear-shaped', 1
As this shows, both commands do more or less the same thing. Here are some reasons to use one or the other:
Using RAISERROR
When you call RAISERROR, the main arguments are:
- Either a message id number, or the text of the message to be displayed.
- The severity level of the message.
- The state (nearly always just 1).
Here's our simple example above:
-- raise error of severity level 15
RAISERROR('This went pear-shaped',15,1);
So this would raise an error of severity level 15 (still enough to end the query in question). Alternatively, we could show a system error message:
-- Show message: "the article resolver supplied
-- is either invalid or nonexistent"
RAISERROR(20020,12,1)
Finally, you can create your own parametrised error message:
-- show a message using parameters
RAISERROR('Star sign %s limited to %i queries per day',
16, 1, 'Scorpio', 10)
This would display the following message:
The main symbols that you can use are as follows:
- %d or %i: which means Any integer
- %s: which means Any string of text
The parameters have to come in the right order, and be of the right type (so in the example above, Scorpio is assigned to the first parameter, which is a string of text, and 10 to the second, which is an integer).
There's quite a bit more you can do with formatting parameters within error messages - if you know the printf statement in C you'll be able to guess what's possible (otherwise, you're not missing anything, believe me!).
Using THROW
If you have SQL Server 2012, you're probably better off using THROW instead of RAISERROR. The only arguments you can pass to this are:
- A message id number (or a variable containing a number).
- The message text to be displayed (often built up in a variable using FORMATMESSAGE - see below for an example).
- The state (nearly always just 1).
- Remember that THROW always creates errors with severity level 16.
Here's an example of using THROW to display a customised error message:
-- add a message to the system messages table
EXEC sys.sp_addmessage
@msgnum=60001,
@severity = 16,
@msgtext = N'No %s or %s can run queries after %i hours.'
GO
-- create a variable to hold message text
DECLARE @WolErrorMessage nvarchar(2048) = FORMATMESSAGE(60001,
'Scorpio','Aquarius', 17);
-- previous line must end in semi-colon
THROW 60000, @WolErrorMessage, 1;
Here's what this would display when run:
You can also use sys.sp_addmessage to add messages for display using RAISERROR, in a similar way.