dcsimg

Error handling in SQL Server 2005

January 5, 2007

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers