Error Handling sql

Question

Hi,

I would like to handle error severity in catch block for stored proc

ERROR_SEVERITY() is used but its returns no like 16, and so, but I need to display text which indicate corresponding error

Thank you

Kirtesh Shah · Answer

Hi, You can use ERROR_MESSAGE() to display error message with severity. See below code for more details, BEGIN TRY -- Generate a divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; END CATCH; GO Result ----------- (0 row(s) affected) ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage ----------- ------------- ----------- --------------- ----------- ---------------------------------- 8134 16 1 NULL 4 Divide by zero error encountered. (1 row(s) affected)

Vinitha T · Answer

ERROR_SEVERITY() returns severity of the error and its return type is int. Error message by SEVERITY indicates the severity level of the type of problem. There are almost 26 severity levels. Severity level 0 to 10 -> Informatioal messages and not actual errors. 11 to 16 -> User problems, fixed by user. 18 -> Nonfatal internal software problem 19 -> Nonconfigurable resource limit has been exceeded. and goes on. As your return number is 16, It is general errors that can be corrected by the user.

Jignesh Kumar · Answer

Hi Jayu, Please find below article which will help you to undestand step by step, https://www.c-sharpcorner.com/UploadFile/f0b2ed/exception-handling-in-sql-server/

Sachin Singh · Answer

USE yourDatabaseName GO BEGIN TRY -- Generate a divide-by-zero error SELECT 1 / 0 AS Error; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_STATE() AS ErrorState, ERROR_SEVERITY() AS ErrorSeverity, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; END CATCH; GO

Vikas Panwar · Answer

BEGIN TRY -- Write statements here that may cause exception END TRY BEGIN CATCH -- Write statements here to handle exception END CATCH