With SQL Server 2000, we do have Exception Handling using @@Error variable buy this is very cumbersome when you have multiple statements.
Now SQL Server 2005 provides you great deal of implementing Exception Handling with ease of process like Exception handling in other programming languages.
SQL Server 2005 comes with Try and Catch block exception handling. (Whenever an exception occurred in any of TRY block statements, the process will goes to Catch block.)
TRY BLOCK : put all your processing code here
CATCH BLOCK : write the handling mechanism like auditing.
In Catch block, we use the following error functions to fetch the exception details in-depth.
ERROR_NUMBER() : Represents Error Number,
ERROR_SEVERITY() : Represents Severity of the Error,
ERROR_STATE() : Represents State number of the Error
ERROR_PROCEDURE() : Represents thename of the stored procedure or trigger caused the
CATCH block of a TRY
ERROR_LINE() : Line number where error occured,
ERROR_MESSAGE() : Message of the error which helps to take action;
Example
Following stored procedure executes multiple SQL statements and uses TRY...CATCH block to catch
errors.
BEGIN
BEGIN TRY
--- Your Statements---
End Try
BEGIN CATCH
DECLARE @ErrMsg NVARCHAR(MAX)
DECLARE @ErrSeverity NVARCHAR(MAX)
DECLARE @ErrProc NVARCHAR(MAX)
DECLARE @ErrState INT
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrProc = ERROR_PROCEDURE(), @ErrSeverity = ERROR_SEVERITY(), @ErrState = ERROR_STATE()
RAISERROR (@ErrMsg,@ErrSeverity, @ErrState)
END CATCHEND
Summary
SQL Server 2005 augments and ease the development process with new Exception Handling techniques.
For more information on Exception handling,
http://msdn2.microsoft.com/en-us/library/ms179296.aspx
|
No responses found. Be the first to respond and make money from revenue sharing program.
|