BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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!
- Error-handling and error messages (this blog)
- The parts of an error
- A practical example of error-handling
- 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.
Error-handling and error messages
This blog will explain what to do when your SQL query goes pear-shaped!
Before SQL Server 2005, the only practical way to trap errors in SQL was using the old-fashioned @@error system variable. Although this is still supported, the ways of handling errors shown in this blog are much better, and should be used in preference.
What is an Error?
Let's create an error! Consider the SQL excerpt below, which tries to squeeze a quart into a pint pot (that English expression may not travel well ...).
-- create an integer variable
DECLARE @i int
-- try to store text in it
SET @i='owl'
When you run this, you'll see this error:
You can't store text in an integer variable!
When you double-click on the error message, SQL will highlight the offending line:
Double-click on any error message to highlight the statement which caused the error.
Error information captured
For any error, SQL Server stores the following bits of information:
| Information | Our example | Notes |
|---|---|---|
| Error number | 245 | The unique error message number. |
| Level | 16 | The severity of the error. |
| State | 1 | The state of the error (usually 1). |
| Message | Conversion failed when ... | The text of the error. |
| Line number | 6 | The number of the offending line. |
| Procedure | Not applicable here. | The name of any stored procedure. |
All of these are described in much more detail in the next part of this blog.
Trapping errors using TRY ... CATCH
Other languages allow you to trap errors - here are a couple of examples:
| Language | Syntax |
|---|---|
| VBA | ON ERROR GOTO ... |
| C# | try { ... } catch { ... } |
From SQL Server 2005 onwards, you can at last do this in SQL:
BEGIN TRY
-- try doing something dodgy
END TRY
BEGIN CATCH
-- what to do if it goes wrong
END CATCH
There are two important things to notice about this syntax:
- There is no FINALLY clause in SQL (although if you don't program in other languages, you won't miss it!).
- You can't omit the CATCH clause, even if you don't need or want one!
Here's a simple example of the use of error-handling:
BEGIN TRY
-- create an integer variable
DECLARE @i int
-- try to store text in it
SET @i='owl'
END TRY
BEGIN CATCH
-- set variable to 1 instead
SET @i = 1
END CATCH
SELECT @i
This would show the following output when run:
Having failed to store owl in the variable, we successfully store 1 instead.
It's time now to look at the various parts of an error in more detail, then I'll give some practical examples of error-handling in SQL.
- Error-handling and error messages (this blog)
- The parts of an error
- A practical example of error-handling
- Raising Errors