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!

  1. Error-handling and error messages (this blog)
  2. The parts of an error
  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.

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:

Error message 245

You can't store text in an integer variable!

When you double-click on the error message, SQL will highlight the offending line:

Error 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:

  1. There is no FINALLY clause in SQL (although if you don't program in other languages, you won't miss it!).
  2. 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:

Variable is set to 1

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.

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