Looking into exceptions in SQL Server

In this post I would like to talk about exceptions in SQL Server. I know there are samples of how to handle exception in SQL Server in BOL,MSDN and elsewhere but I thought that it would be a good idea to give it a try with hands-on examples.

Well, let me make a general statement. All humans make mistakes.That includes application developers,database developers e.t.c.

So all developers must be familiar with the various exception mechanisms and tools that each language has. Everyone who has worked with Visual Basic has used the “ON  ERROR GOTO” statement and all .NET languages have exception handling techniques e.g Try Catch statements-blocks.

Since SQL Server 2005 we have support for TRY…CATCH statements. That makes it much easier and less verbose to handle exceptions in SQL Server. .Net developers find it much easier to use Try Catch statements in SQL Server and in general it is a much readable (cleaner syntax) format to handle exceptions.

I am sure that most of you are familiar with Try Catch statements but before I go on with the actual examples I will explain it. Inside the TRY block all the instructions that might cause an exception are included. When an exception occurs from one of the statements that are inside in the TRY block, the control is handed to the CATCH block, where the exception can be handled (by printing a message to the user, log the error e.t.c)

In previous versions SQL Server had various options to handle errors and exceptions. We had the @@error global variable.If the @@error value was zero we had no error.If it was not zero we should firstly find the value of the global variable and understand what kind of error it was.

Let’s have a look at all these exception handling techniques with some examples.We will need an instance of SQL Server running in our machine.You can download and install the free SQL Server Express edition from here.I will use SQL Server 2008 R2 Express edition.

1) In this first example, I will show you what happens when we do not use exception handling mechanisms in T-SQL. Launch SSMS and connect to the instance of SQL Server you have available.

In a new query window type the following T-SQL statements.

USE TEMPDB
GO

CREATE TABLE dbo.Football
(
TeamID INT PRIMARY KEY ,
TeamName VARCHAR(50) NOT NULL ,
TeamCoach VARCHAR(50) NOT NULL ,
TeamCaptain VARCHAR(50) NOT NULL
) ;
GO

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'ManUtd','Alex Ferguson','Nemanja Vidić');

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'Liverpool','Kenny Dalglish','Steven Gerrard');

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (2,'Everton','David Moyes','Phil Neville');

--execute this after you have finished with the example
drop table dbo.football

Execute the code.As you will notice, there is a primary key violation. Since there is not an exception handling mechanism SQL Server has little choice but to send me back (to the calling (client) application) the very detailed error which follows.

“Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint ‘PK__Football__123AE7B907020F21’. Cannot insert duplicate key in object ‘dbo.Football’. The duplicate key value is (1).
The statement has been terminated.”

As you can understand the client cannot do anything about that. He will be confused and angry. A malicious user can even get valuable information about our database,system and that is not something we want.You will still notice that the 2 of the 3 possible rows are inserted into the table despite the primary key violation

2) We can rewrite the previous query using TRY CATCH statements. In this example we will not do something fancy. I would like to demonstrate the exception functions and how Τry Catch works as well.Type in a new query window the following

USE tempdb

go

CREATE TABLE dbo.Football
(
TeamID INT PRIMARY KEY ,
TeamName VARCHAR(50) NOT NULL ,
TeamCoach VARCHAR(50) NOT NULL ,
TeamCaptain VARCHAR(50) NOT NULL
) ;
GO

BEGIN TRY
INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'ManUtd','Alex Ferguson','Nemanja Vidić');

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'Liverpool','Kenny Dalglish','Steven Gerrard');

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (2,'Everton','David Moyes','Phil Neville');
END TRY
BEGIN CATCH

SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;

END CATCH
GO
--execute this after you have finished with the example
drop table dbo.football

Execute the code above. You will notice that now instead of 2 rows inserted in the table, there is only one inserted. This happens because after lines 18-19 fail, the execution of the code (flow) is handed to the line 24 (Catch statement) and that means that lines 21 and 22 are never executed.Inside the Catch statement I am not doing anything special. I just use the built-in exception functions to print out the error number, the error severity,the error state, the error line and the error message.Those exception functions are almost self explanatory and you can find their full explanation at BOL.

3)Let’s try our next example. In order to follow this example it would be great if you understand the basics of transactions. I have another post on this subject.Click here if you want to have a look.

Τype the following T-SQL statements in a new query window. In this example I have placed the Insert statements inside a transaction.

USE tempdb

go

CREATE TABLE dbo.Football
(
TeamID INT PRIMARY KEY ,
TeamName VARCHAR(50) NOT NULL ,
TeamCoach VARCHAR(50) NOT NULL ,
TeamCaptain VARCHAR(50) NOT NULL
) ;
GO

BEGIN TRANSACTION
INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'ManUtd','Alex Ferguson','Nemanja Vidić');

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'Liverpool','Kenny Dalglish','Steven Gerrard');

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (2,'Everton','David Moyes','Phil Neville');
COMMIT TRANSACTION

SELECT * FROM DBO.FOOTBALL
--execute this after you have finished with the example
drop table dbo.football

When you execute the code above, you will get the same error (primary key violation) but stilll 2 rows will be inserted in the table.You will be consfused by this behaviour.We have certain kind of errors that are considered not to be fatal.So in this case we have the error taking place but then the code proceeds to the next statements and executes them successfully. Some people do not like this behaviour and we can most certainly influence this behaviour.In this next example I will set the XACT_ABORT property to ON. The default value is OFF. By setting this property at the connection level to ON we are adopting a new strategy which says that “if an error takes place in a transaction or an exception is thrown, the whole batch is aborted.The whole transaction is rolled back”. In a new query window type the following T-SQL statements

USE tempdb

go

CREATE TABLE dbo.Football
(
TeamID INT PRIMARY KEY ,
TeamName VARCHAR(50) NOT NULL ,
TeamCoach VARCHAR(50) NOT NULL ,
TeamCaptain VARCHAR(50) NOT NULL
) ;
GO

SET XACT_ABORT ON

BEGIN TRANSACTION
INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'ManUtd','Alex Ferguson','Nemanja Vidić');

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'Liverpool','Kenny Dalglish','Steven Gerrard');

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (2,'Everton','David Moyes','Phil Neville');
COMMIT TRANSACTION

SELECT * FROM DBO.FOOTBALL
--execute this after you have finished with the example
drop table dbo.football

Execute the code above. When you run the Select statement you will see that no rows were inserted in the table.

4) Let’s move on with our next example. I will follow the previous example and I will show you a way to handle exceptions before SQL Server 2005 and Try Catch statements were available.In a new query window type the following T-SQL statements.

USE tempdb

go

CREATE TABLE dbo.Football
(
TeamID INT PRIMARY KEY ,
TeamName VARCHAR(50) NOT NULL ,
TeamCoach VARCHAR(50) NOT NULL ,
TeamCaptain VARCHAR(50) NOT NULL
) ;
GO

SET XACT_ABORT OFF

DECLARE @myerror int

BEGIN TRANSACTION

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'ManUtd','Alex Ferguson','Nemanja Vidić');

SET @myerror=@@ERROR
IF (@myerror!=0)GOTO Errorhandler

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'Liverpool','Kenny Dalglish','Steven Gerrard');

SET @myerror=@@ERROR
IF (@myerror!=0)GOTO Errorhandler

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (2,'Everton','David Moyes','Phil Neville');

SET @myerror=@@ERROR
IF (@myerror!=0)GOTO Errorhandler

COMMIT TRANSACTION

GOTO NEXT
Errorhandler:
RAISERROR ('we have an error',17,21);
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION NEXT:

SELECT * FROM DBO.FOOTBALL

--execute this after you have finished with the example
drop table dbo.football

Execute the code above. In this case nothing will be inserted in the table.You will get the same primary key violation along with our custom message.

We check after every INSERT statement to see if there is an error.We do that by checking the value we get back from the @@error global variable. If there is an error (error!=0) we pass the control to the Errorhandler block . If there is no error (error=0) we proceed to the next line.If there is no error the transaction is commited.If there is an error we do raise a custom error  by using the raiserror function.Then we check to see if there are any active transactions and rollback those transactions.Obviously this is a very verbose piece of T-SQL code. We have to check for errors after each INSERT statement.We will rewrite the code above using Try Catch statements.

5) In a new query window type the following T-SQL statements.

USE tempdb

go

CREATE TABLE dbo.Football
(
TeamID INT PRIMARY KEY ,
TeamName VARCHAR(50) NOT NULL ,
TeamCoach VARCHAR(50) NOT NULL ,
TeamCaptain VARCHAR(50) NOT NULL
) ;
GO

BEGIN TRY
BEGIN TRANSACTION

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'ManUtd','Alex Ferguson','Nemanja Vidić');

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'Liverpool','Kenny Dalglish','Steven Gerrard');

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (2,'Everton','David Moyes','Phil Neville');

COMMIT TRANSACTION
END TRY

BEGIN CATCH
RAISERROR ('we have an error',17,21);
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH

SELECT * FROM DBO.FOOTBALL

--execute this after you have finished with the example
drop table dbo.football

Execute the code above. In this case nothing will be inserted in the table.You will not get the primary key violation message this time.By that I mean that the message that SQL Server would send back to the client is never sent because only the messages in the Catch block will be printed out to the client,just the custom message we raise with the raiserror function.Have a good look at the code and see how much cleaner syntax we have used. Surely it rings a bell with the .Net developers.

6) This is the last example I would like to show you. I will show you how to use exceptions in a batch where we have various INSERT statements and there is a trigger also applied to the table.

Type the code below in a new query window. When  I say type, I mean copy paste the code. In every snippet of code I provide, you can use the icons in the upper right-hand corner to copy the code.

USE tempdb

go

CREATE TABLE dbo.Football
(
TeamID INT PRIMARY KEY ,
TeamName VARCHAR(50) NOT NULL ,
TeamCoach VARCHAR(50) NOT NULL ,
TeamCaptain VARCHAR(50) NOT NULL
) ;
GO

--create a trigger that does not allow to enter teams with ID = 4
CREATE TRIGGER TeamID4 ON dbo.football
FOR INSERT
AS
BEGIN
BEGIN TRANSACTION
DECLARE @teamid4 INT
SELECT @teamid4 = COUNT(*) FROM INSERTED WHERE TeamID=4
IF @teamid4 !=0
ROLLBACK TRANSACTION
END

--insert some values that will make the trigger fire

BEGIN TRANSACTION

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (4,'ManUtd','Alex Ferguson','Nemanja Vidić');

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'Liverpool','Kenny Dalglish','Steven Gerrard');

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (2,'Everton','David Moyes','Phil Neville');

COMMIT TRANSACTION

--no values will be inserted
SELECT * FROM DBO.FOOTBALL

--use old school exception handling techniques to get the exception
-- the custom error we want to print out will not get printed to the client

BEGIN TRANSACTION

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (4,'ManUtd','Alex Ferguson','Nemanja Vidić');

IF (@@error !=0) PRINT 'an error has occured'

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'Liverpool','Kenny Dalglish','Steven Gerrard');
IF (@@error !=0) PRINT 'an error has occured'

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (2,'Everton','David Moyes','Phil Neville');
IF (@@error !=0) PRINT 'an error has occured'

COMMIT TRANSACTION

--we will use try catch statements this time and the error is printed to client

BEGIN TRY
BEGIN TRANSACTION

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'ManUtd','Alex Ferguson','Nemanja Vidić');

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (1,'Liverpool','Kenny Dalglish','Steven Gerrard');

INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain)
VALUES (2,'Everton','David Moyes','Phil Neville');

COMMIT TRANSACTION
END TRY

BEGIN CATCH
RAISERROR ('we have an error',17,21);
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH

--no values will be inserted in the table
SELECT * FROM DBO.FOOTBALL

--drop the trigger

DROP TRIGGER TeamID4

--execute this after you have finished with the example

drop table dbo.football

We need to execute the code step by step.First execute the lines 01-24. We create in the tempdb database a table (football) and then we create a trigger that does not allow to enter in the table rows where the TeamID equals 4.Then execute the code in lines 28-39. The trigger will fire and then we will get an error message like this “Msg 3609, Level 16, State 1, Line 3
The transaction ended in the trigger. The batch has been aborted.“If you execute line 42, you will see that naturally no rows were inserted.The trigger fired when the exception took place and rolled back the whole transaction.

Execute the code in lines 47 to 64. This time we use the @@error global variable to check for exceptions.But we will not get our custom error message printed out to the client.When the code executes line 50 then the exception happens and the trigger fires. Nothing after that line is executed. The body of the trigger rollbacks the whole transaction and no lines after 50 are executed.

Now execute lines 69 to 89 . We basically execute the same code but now we use try catch statements.Still there are no rows inserted in the table.But this time we get our custom error to get printed to the client.In my case is “Msg 50000, Level 17, State 21, Line 18 we have an error“.

So by that you can see that in certain cases we have much more power on how to handle exceptions using try catch statements rather than the @@error global function.The trigger rollback the transaction but still because of how try catch statements work we handle the exception the way we want.

Then we need to drop the trigger and drop the table.

Hopefully you understand how important is to use Try Catch blocks in your T-SQL code.

Hope it helps!!!