Error Handling sql

jayu p

4y1.3kSQL

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vinitha T4y ago
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.
Sachin Singh4y ago
1. USE yourDatabaseName
2. GO
5. BEGIN TRY
6. -- Generate a divide-by-zero error
7. SELECT
8. 1 / 0 AS Error;
9. END TRY
10. BEGIN CATCH
11. SELECT
12. ERROR_NUMBER() AS ErrorNumber,
13. ERROR_STATE() AS ErrorState,
14. ERROR_SEVERITY() AS ErrorSeverity,
15. ERROR_PROCEDURE() AS ErrorProcedure,
16. ERROR_LINE() AS ErrorLine,
17. ERROR_MESSAGE() AS ErrorMessage;
18. END CATCH;
19. GO

Jignesh Kumar4y ago

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/

Kirtesh Shah4y ago

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)

Vikas Panwar4y ago

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