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
|
Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..
|
