In SQL- Transact, The TRY CATCH are catches all errors and store in
the @ERRORS variable and raise the errors using
the RAISERROR().
Following functions are used in CATCH block,
1.
ERROR_NUMBER():
Will return error number.
2.
ERROR_SEVERITY():Will
return severity level.
3.
ERROR_STATE():Will
return state number.
4.
ERROR_LINE():Will
return error line number.
5.
ERROR_PROCEDURE():Will
return stored procedure name that occurred the error.
6.
ERROR_MESSAGE():Will
return full message text of the error.
BEGIN TRY
YOUR CODE HEARE
END TRY
BEGIN CATCH
YOURE CODE TO
HANDLE ERRORS
END CATCH
Example for Exception/Error Handling in Stored Procedure using
TRY/CATCH
DECLARE @Error_Number BIGINT,
@Error_Severity BIGINT,
@Error_State BIGINT,
@Error_Procedure NVARCHAR(500),
@Error_Line BIGINT,
@Error_Message NVARCHAR(MAX),
@Create_Date DATETIME,
@Created_By NVARCHAR(255)
BEGIN TRY
SELECT 100/0
END TRY
BEGIN CATCH
SELECT @Error_Number = ERROR_NUMBER(),
@Error_Severity
= ERROR_SEVERITY(),
@Error_State = ERROR_STATE(),
@Error_Procedure = ERROR_PROCEDURE(),
@Error_Line = ERROR_LINE(),
@Error_Message
= ERROR_MESSAGE(),
@Create_Date =GETDATE(),
@Created_By ='Anil'
EXEC sp_InsertErrorDetails(@Error_Number, @Error_Severity, @Error_State, @Error_Procedure, @Error_Line, @Error_Message, @Create_Date, @Created_By)
END CATCH
