Even though @@Error is good for handling errors, it cannot handle exceptions, indeed in sql server 2000, there are no ways to do this. In SQL server 2005, Microsoft introduced a new feature “try catch” dedicated for this purpose. For example the divide by zero exception, even before the @@Error returns the error id, SQL throws an exception.
DECLARE @i int , @j int
SELECT @i =100, @j =0
SELECT @i/@j
Print @@Error
Now if you run this in Query analyzer, you will be able to see both the Error message and the Error value.
Now let us see, how to handle this is SQL Server 2005. SQL server 2005 introduces new Statements to handle this.
BEGIN TRY
<Statements to be handled>
END TRY
BEGIN CATCH
<Statements in case of Exception>
END CATCH
Don’t put any other statements in between the “End TRY and the ‘Begin catch’ statements. For example consider the modified version of the above code.
DECLARE @i int , @j int
SELECT @i =100, @j =0
BEGIN TRY
SELECT @i/@j
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() as ErrorNo,
ERROR_MESSAGE() as ErrMessage
END CATCH
Now, if you look at this query, we have placed the Division part inside a BEGIN TRY – END TRY statement, and in case of an error, instead of throwing an error message directly, execution comes inside the BEGIN CATCH – END CATCH statement and start executing the statements inside that. In the above example, I put a select statement, which returns the error number and the error message.
