564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
How error messages work in SQL (including TRY, CATCH, RAISERROR and THROW) Part one 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!
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. |
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.
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.
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.
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:
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.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.