Exception Handling using Try Catch in SQL Server Stored Procedures

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.

USE OF TRY CATCH IN SQL SERVER

BEGIN TRY

YOUR CODE HEARE

END TRY

BEGIN CATCH

YOURE CODE TO HANDLE ERRORS

END 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

Result :

                          You Might Also Like                
                         