This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! 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.

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!

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

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.

Parts of this blog
Error-handling and error messages (this blog) The parts of an error A practical example of error-handling Raising Errors

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC