Using a TRY CATCH BLOCK IN SQL CODE FOR trapping Errors
When a stored procedure is executed in unattended mode, it could throw one or many error or exceptions. There is more than one way to try and catch this errors and send it as a mail to the users when stored procedure fails to execute. Today I will discuss one method to display this message using two important SQL Functions/Tasks.
Step 1: Catch the exception in a container and store in SQL table
Step 2: Mail the information in the container to the users
Step1 : Catch the exception: For this we often use the TRY CATCH BLOCK WITHIN THE SQL Stored Procedure.
Example
CREATE/ALTER PROCEDURE <SCHEMA NAME>.< NAME OF PROCEDURE>
AS
SET NOCOUNT ON
BEGIN TRY
<Logic for the Stored Proc is inserted here>
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER()
, ERROR_SEVERITY()
, ERROR_STATE()
, ERROR_PROCEDURE()
, ERROR_LINE()
, ERROR_MESSAGE()
To do this we need to create a SQL Table as under:
CREATE TABLE ErrorLog
{
@error_number INT,
@error_severity INT,
@erro_state INT,
@procedure_name VARCHAR(MAX),
@error_line INT,
@error_message VARCHAR(MAX)
}
INSERT INTO ERRORLOG
VALUES
SELECT @error_number = ERROR_NUMBER()
, @error_severity = ERROR_SEVERITY()
, @erro_state = ERROR_STATE()
, @procedure_name = ERROR_PROCEDURE()
, @error_line = ERROR_LINE()
, @error_message = ERROR_MESSAGE()
–SELECT @emess AS ErrorMessage, @eline AS ErrorLine
EXECUTE [usp_ErrorLogInsert]
@ERROR_NUMBER ,
@ERROR_SEVERITY ,
@ERROR_STATE ,
@ERROR_PROCEDURE ,
@ERROR_LINE ,
@ERROR_MESSAGE
END CATCH;
If we want to catch the error messages into SQL server table, we need the following steps
Step 1: Create a Error Log Table on SQL Server. We need appropriate table create permissions to
create a permanent table on SQL Server.
–Create Table
CREATE TABLE ErrorLog
{
Error_number INT,
Error_severity INT,
ErrorSstate INT,
ProcedureName VARCHAR(MAX),
ErrorLine INT,
@error_message VARCHAR(MAX),
}
Step 2 : Create a procedure to insert data by called procedure. The called procedure will use this procedure to send data and populate table
CREATE Procedure [dbo].[usp_ErrorLogInsert]
( @ERROR_NUMBER int
,@ERROR_SEVERITY int
,@ERROR_STATE int
,@ERROR_PROCEDURE AS varchar(max)
,@ERROR_LINE AS int
,@ERROR_MESSAGE varchar(max)
)
AS
Set Nocount on
INSERT INTO [dbo].[ErrorLog]
([ERROR_NUMBER]
,[ERROR_SEVERITY]
,[ERROR_STATE]
,[ERROR_PROCEDURE]
,[ERROR_LINE]
,[ERROR_MESSAGE])
VALUES
( @ERROR_NUMBER
,@ERROR_SEVERITY
,@ERROR_STATE
,@ERROR_PROCEDURE
,@ERROR_LINE
,@ERROR_MESSAGE)
TRAINING GUIDE 