python ruby-programming-language Smalltalk Vue.js Knockoutjs TypeScript JavaScript, OOPs JavaScript Angular 8,7,6,5,4 and 2 Interview Questions and Answers Angular 8,7,6,5,4 and 2 Interview Questions and Answers Angular 4 Angular 2 AngularJs 1.x NodeJs Perl Programming R Programming RequireJs Rust Programming Backbonejs closure programming language go-programming-language kotlin-programming-language
Error Handling in SQL Server Stored Procedure using TRY/CATCH

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 :



ANIL SINGH

Hi there! Welcome to my blog. My name is Anil Singh. I'm a author of this blog. For more detail about me, kindly refer to this link..
My Blogs - http://code-sample.com and http://code-sample.xyz
My Books - Interview Questions and Answers Books- Get Your Book in 15+ Digital Stores Worldwide..

You Might Also Like
www.code-sample.com/. Powered by Blogger.
ASK Questions