Web developers wiki ASP.NET Sitecore Sharepoint Kentico by Evident Interactive

Try-catch in SQL Server 2005

Modified: 2008/01/30 12:11 by vanthoog - Categorized as: SQL Server
This article gives a brief introduction to using try-catch in SQL Server 2005.

The examples in this article use the following table, which is always empty before any example is executed:

create table person
(
	firstname nvarchar(16),
	lastname nvarchar(16),
	address nvarchar(16),
	city nvarchar(16)
	
)

Before SQL Server 2005 trapping and handling runtime errors in sql-code was cumbersome. The only thing you could do was to test the contents of the global variable @@ERROR. But because this global variable is reset after every sql-statement you would have to put an if after every sql-statement. So you would get sql-code like this:

insert into person values ( 'Bill' , 'Doe' , 'Capitol Hill 12345B' , 'Washington D.C.' )

if (@@ERROR <> 0)
begin
	print ''
	print 'Insert failed'
	print ''
end

insert into person values ( 'Julia' , 'Doe' , 'Capitol Hill 12345B' , 'Washington D.C.' )

if (@@ERROR <> 0)
begin
	print ''
	print 'Insert failed'
	print ''
end

In SQL Server 2005 the try-catch syntax was introduced, which provides a more convenient mechanism for trapping errors. Try-catch in SQL Server 2005 is similar to try-catch in C#, although there are some differences (for example, there is no finally). You basically put your sql-code within the try-block and when an error occures processing jumps to the code in the catch-block. For example:

begin try

	insert into person values ( 'Bill' , 'Doe' , 'Capitol Hill 12345B' , 'Washington D.C.' )

end try
begin catch

	print ''
	print 'Insert failed'
	print ''

end catch


Insert failed


In this simple example the insert fails (because the third string is too long) and processing jumps to the catch-block which prints a simple message.

A number of functions have been added in SQL Server 2005 to allow you to capture information about the error which has just occured. These functions are: ERROR_NUMBER() returns the error number. ERROR_MESSAGE() returns the complete text of the error message. ERROR_SEVERITY() returns the error severity. ERROR_STATE() returns the error state number. ERROR_LINE() returns the line number inside the routine that caused the error. ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

Here is a simple example showing the output of these functions:

begin try

	insert into person values ( 'Bill' , 'Doe' , 'Capitol Hill 12345B' , 'Washington D.C.' )

end try
begin catch

	print ''
	print 'Insert failed'
	print ''

	SELECT	ERROR_NUMBER() AS ErrorNumber
	SELECT	ERROR_SEVERITY() AS ErrorSeverity
	SELECT	ERROR_STATE() AS ErrorState
	SELECT	ERROR_PROCEDURE() AS ErrorProcedure
	SELECT	ERROR_LINE() AS ErrorLine
	SELECT	ERROR_MESSAGE() AS ErrorMessage

end catch

Insert failed
 
ErrorNumber
-----------
8152

(1 row(s) affected)

ErrorSeverity
-------------
16

(1 row(s) affected)

ErrorState
-----------
4

(1 row(s) affected)

ErrorProcedure
---------------------------------------------------------------------------------
NULL

(1 row(s) affected)

ErrorLine
-----------
3

(1 row(s) affected)

ErrorMessage
----------------------------------------------------------------------------------
String or binary data would be truncated.

(1 row(s) affected)

Using try-catch is particularly useful when using transactions. In this case it is important to put the “begin transaction” and the commit and rollback at the right place. This example shows the proper way to use try-catch in combination with a transaction:

begin try
	begin transaction

	insert into person values ( 'John' , 'Jones' , 'Rodeo drive 10' , 'Beverly Hills' )

	insert into person values ( 'Bill' , 'Doe' , 'Capitol Hill 12345B' , 'Washington D.C.' )

	commit transaction
end try
begin catch

	print ''
	print 'Insert failed'
	print ''
	print 'Contents before the rollback'
	print ''

	select * from person

	rollback transaction

	print 'Contents after the rollback'
	print ''

	select * from person

end catch


(1 row(s) affected)
 
Insert failed
 
Contents before the rollback
 
firstname        lastname         address          city
---------------- ---------------- ---------------- ----------------
John             Jones            Rodeo drive 10   Beverly Hills

(1 row(s) affected)

Contents after the rollback
 
firstname        lastname         address          city
---------------- ---------------- ---------------- ----------------

(0 row(s) affected)

In this example the first insert succeeds, but the second insert fails. The select-statement before the rollback shows that one record has been inserted. The select-statement after the rollback returns no rows, because all inserts have been rolled back.

 © Evident Interactive BV