Error handling in SQL Server 2005

In SQL Server 2005, error handling was made easy. This article illustrates various methods of handling errors using the TRY and CATCH command and various ERROR_ functions.


Catching the Error line number in a SQL Statement


When you execute bunch of SQL statements and need to know on which line the error occurred, then you could do something similar to what is shown below. TRY … CATCH catches all the errors with a severity greater than 10 at execution.


SQL Statement

Set quoted_identifier off
Go
Use Tempdb
Go
begin try — line 1
declare @i tinyint — line 2
select @i=count(*) from sys.sysobjects — line 3
select @i*999999999 as myval — line 4
end try — line 5
begin catch — line 6
select — line 7
error_line() as errornumber — line 8
end catch — line 9

Results

errornumber
———–
4
(1 row(s) affected)

Catching the Error line number in a procedure


Let us create a procedure as shown below.

Set quoted_identifier off
Go
Use tempdb
go
IF OBJECT_ID ( ‘MyProc’, ‘P’ ) IS NOT NULL
DROP PROCEDURE MyProc;
GO
Create procedure MyProc as — line 1
declare @i tinyint — line 2
select @i=count(*) from sys.sysobjects — line 3
select @i*999999999 as myval — line 4
Go

Now let us execute the procedure using TRY and CATCH as shown below.


SQL Statement

Set quoted_identifier off
Go
Use tempdb
go
BEGIN TRY
exec Myproc
END TRY
begin catch
select
error_line() as errornumber
end catch

Results

errornumber
———–
4
(1 row(s) affected)

SQL Server 2005 also added new error handling functional tools to provide more information on the errors. Those functions are ERROR_NUMBER (), ERROR_SEVERITY (), ERROR_STATE (), ERROR_PROCEDURE (), ERROR_LINE () and ERROR_MESSAGE ().


Let us execute the same procedure using the below TRY and CATCH statement block.


SQL Statement

Set quoted_identifier off
Go
Use tempdb
go
BEGIN TRY
exec Myproc
END TRY
begin catch
select
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
end catch
go

Results [Refer Fig 1.0]
























ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
———– ———— ———- ————– ——– ————–
8115 16 2 MyProc 4 Arithmetic overflow error for data type tinyint, value = 1829.



Fig 1.0


Now let us create another procedure as shown below.

Set quoted_identifier off
Go
Use tempdb
go
IF OBJECT_ID ( ‘MyProc2’, ‘P’ ) IS NOT NULL
DROP PROCEDURE MyProc2;
GO
Create procedure MyProc2 as — line 1
declare @i tinyint — line 2
declare @j tinyint — line 3
select @i=count(*) from sys.sysobjects — line 4
select @j=count(*) from sys.syscolumns — line 5
select @i as I — line 6
select @j as J — line 7
select @i*999999999999 as myval — line 8
Go

Let us try to catch the error when executing the above two procedures, MyProc and MyProc2, that we created recently.


SQL Statement

Set quoted_identifier off
Go
Use tempdb
go
BEGIN TRY
exec Myproc
exec Myproc2
END TRY
begin catch
select
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
end catch
go

Results (Refer Fig 1.1)


























myval
———–

(0 row(s) affected)
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
———– ————- ———- ———— ——– ————–
8115 16 2 MyProc 4 Arithmetic overflow error for data type tinyint, value = 1829.

Note: It tried executing the first procedure and once it got an error, it came to the catch part to capture the errors.




Fig 1.1


Explanation of the ERROR FUNCTIONS


ERROR_NUMBER (): This returns the actual error number from the sys.sysmessages . This is similar to @@error, which returns the error number. Eg: select * from sys.sysmessages where error=8115 and msglangid=1033


ERROR_SEVERITY (): This returns the actual severity level of the error from the sys.sysmessages . Eg: select Severity from sys.sysmessages where error=8115 and msglangid=1033


ERROR_STATE (): This returns the actual severity state of the error caused by the SQL statement.


ERROR_PROCEDURE (): Returns the name of the stored procedure or trigger where an error occurred and was caught by TRY … CATCH.


ERROR_LINE (): This returns the line number at which an error occurred and was caught by the TRY… CATCH construct.


ERROR_MESSAGE (): This returns the actual error message text of the error that was caught by TRY… CATCH construct. This gets the information from sys.sysmessages. Eg: select description from sys.sysmessages where error=220 and msglangid=1033


Conclusion


This article illustrated various methods of handling errors using the TRY and CATCH commands and various ERROR_ functions.


» See All Articles by Columnist MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles