Being a .Net developer, the most useful feature i found was the Error Handling or Exception Handling technique.When it comes to programming languages , all popular OOP languages have TRY()…Catch() method to handle an exception.
In Sql Server 2005, error handling was made easy . This article demonstrates various methods of handling errors using
Try()..Catch() in T-SQL.
Here you try something like that…
declare @i tinyint
select @i=count(*) from sys.sysobjects
select @i*999999999 as myval
When you try to run this you will get a error like this…
-- Error
Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 1812.
(1 row(s) affected)
Now try to run it something like this….
begin try
declare @i tinyint
select @i=count(*) from sys.sysobjects
select @i*999999999 as myval
end try
begin catch
select
error_line() as errornumber
end catch
you will get result like
errornumber
------------------
4
so now this time it throws the number of error.
You can also get more parameters like Error_Line from this like,,,
begin try
declare @i tinyint
select @i=count(*) from sys.sysobjects
select @i*999999999 as myval
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
like this……
Now you can also make a Store Procedure like this which throws error and use this in your try()..catch() statement like this…
CREATE PROCEDURE Sp_ThrowErrorInfo
AS
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;
GO
then try like this..
BEGIN TRY
declare @i tinyint
select @i=count(*) from sys.sysobjects
select @i*999999999 as myval
END TRY
BEGIN CATCH
Exec Sp_ThrowErrorInfo
END CATCH;
This gives you same results as above.
You can also use it with a procedure like if you have some kind of procedure than you want to associate with it
Try()…Catch() block then do something like that
First create a Procedure which contains SQL statements to do a specific task, i made a Store procedure which select all the data from a table which doesn’t present in the database
CREATE PROCEDURE Sp_ErrorTest
AS
SELECT * FROM NonexistentTable;
GO
then do like this….
BEGIN TRY
EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
EXECUTE Sp_ThrowErrorInfo
END CATCH;
This will show you error,, so in this way you can use Store Procedures in Error Handling
There are many kind of errors which you able to trace with Try()…Catch() statements in T-SQL
The following system functions can be used to obtain information about the error that CATCH block to be executed:
- ERROR_NUMBER() returns the number of the error.
- ERROR_SEVERITY() returns the severity.
- ERROR_STATE() returns the error state number.
- ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
- ERROR_LINE() returns the line number inside the routine that caused the error.
- ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
If these functions are used outside the CATCH block, then all of them will return NULL value.
You can also use Try()..Catch() statement to handle errors that occur inside a transaction, by checking the value of XACT_STATE . The XACT_STATE function determines whether the transaction should be committed or rolled back.
You can use XACT_STATE_ABORT is on, this makes transaction uncommittable when error occurs..
If XACT_STATE =1 , transaction is committable.
If XACT_STATE=-1, the transaction is uncommittable and should be rolled back.
If XACT_STATE=0, means that there is no transaction and a commit or rollback operation would generate an error.
In this way there is a lot more to do in error handling, and there are commands which give you very fine-grained control over small set of errors.
Thanks..