Error Handling in SQL Server
Handling errors in SQL Server Stored Procedures is very essential and important from the prospective of performance and managing appropriate data in the palliation. In the earlier version of SQL Server handling errors was not so easy task, you could test the value of @@ERROR or check @@ROWCOUNT, but if the error was a fatal error you did not have a lot of options.
With SQL Server 2005, new error handling technique has been introduced with the TRY...CATCH processing. From the Developer’s point view this new technique is very similar to TRY...CATCH processing that is there .Net. First it executes the SQL statement which we have written in the TRY block and if any error occurs, then it will get executed the CATCH block.
Syntax:
BEGIN TRY
// SQL Statements
END TRY
BEGIN CATCH
//Handle the exception details
END CATCH
Below is the list Error handling properties :
1. ERROR_NUMBER() : returns the error number regardless of how many times it is run, or where it is run within the scope of the CATCH block
2. ERROR_STATE() : returns the error state regardless of how many times it is run, or where it is run within the scope of the CATCH block.
3. ERROR_SEVERITY() : returns the severity of the error message that caused the CATCH block to be run
4. ERROR_LINE() : Returns the line number at which the error occurred. Also Returns the line number in a routine if the error occurred within a stored procedure or trigger. Returns NULL if called outside the scope of a CATCH block
5. ERROR_PROCEDURE() : returns the stored procedure name where the error occurred. Returns NULL if the error did not occur within a stored procedure or trigger. Returns NULL if called outside the scope of a CATCH block.
6. ERROR_MESSAGE() : returns the complete text of the error message that caused the CATCH block to be run. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
7. RAISERROR() : CATCH blocks can use RAISERROR to rethrow the error that invoked the CATCH block by using system functions such as ERROR_NUMBER and ERROR_MESSAGE to retrieve the original error information.
Example :
CREATE PROCEDURE Test
AS
BEGIN
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorProcedure NVARCHAR(4000);
DECLARE @ErrorLine INT;
DECLARE @ErrorMessage NVARCHAR(4000);
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorProcedure = ERROR_PROCEDURE(),
@ErrorLine = ERROR_LINE(),
@ErrorMessage = ERROR_MESSAGE();
RAISERROR(
@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
END
GO
