Exception handling in SQL Server

SQL server gives the exception/error handling mechanism which is similar to c#, Try..Catch block. In SQL Server, we can write the SQL Statements within the Try block and if any exception occurred control automatically goes to the group of SQL Statements written inside the next Catch block.
The syntax for Try..Catch is given below:-

BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH
[ ; ]

For example, suppose we have a table employeedetails whose structure is given below:

CREATE table employeedetails (id int identity(1,1),empsignum nvarchar(20), 
empFname nvarchar(100), empEname nvarchar(100), empdate datetime)

Now, for the demo purpose, we can insert or update data in this table with the help of a stored procedure whose script is given below:-

CREATE PROCEDURE Demo_exceptionhandling
(
@empsignum nvarchar(20),
@Fname nvarchar(100),
@Ename nvarchar(100)

)
AS
BEGIN
SET NOCOUNT ON

BEGIN TRY
IF EXISTS (select 1 from employeedetails where empsignum=@empsignum)
BEGIN
Update employeedetails set empFname=@Fname ,empEname=@Ename  where empsignum=@empsignum
END
ELSE
BEGIN
Insert into employeedetails ( empsignum, empFname  ,  empEname  , empdate )
Values (@empsignum, @Fname, @Ename, getdate())
END
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
SET NOCOUNT OFF
END

In the above stored procedure if any error occurs within the try block, it will automatically move to CATCH block where we can handle the error messages with the defined Error Functions in CATCH block. If we want we can save the values return by these error function into a table for future references.These Error Functions are given below:-

ERROR_LINE() – Return error line number of SQL query which cause to raise error.

ERROR_NUMBER() – Return error number which is unique and assigned to it.

ERROR_SEVERITY() – Return severity of error which indicates how serious the error is. The values are between 1 and 25.

ERROR_STATE() – Return state number of error message which cause to raise error.

ERROR_PROCEDURE() – Return name of the procedure where an error occurred.

ERROR_MESSAGE() – Return the complete text of the error message which cause to raise error.

We can also use TRY… CATCH block with transactions where we can write the ROLLBACK command within the CATCH block so that all the uncommitted transactions become rollback in case of any error.

For example, in the below SQL script, we have implemented the transaction in the stored procedure which we have mentioned above.

CREATE PROCEDURE Demo_exceptionhandling
(
@empsignum nvarchar(20),
@Fname nvarchar(100),
@Ename nvarchar(100)

)
AS
BEGIN
SET NOCOUNT ON

BEGIN TRANSACTION

BEGIN TRY
IF EXISTS (select 1 from employeedetails where empsignum=@empsignum)
BEGIN
Update employeedetails set empFname=@Fname ,empEname=@Ename  where empsignum=@empsignum
END
ELSE
BEGIN
Insert into employeedetails ( empsignum, empFname  ,  empEname  , empdate )
Values (@empsignum, @Fname, @Ename, getdate())
END

END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

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
,GETDATE()

END CATCH

IF @@TRANCOUNT > 0
BEGIN
commit transaction
END
SET NOCOUNT OFF
END

TRY…CATCH do not handle the following conditions:
1) Warnings or informational messages that have a severity of 10 or lower.
2) Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session.
3) Attentions, such as client-interrupt requests or broken client connections.
4) When the session is ended by a system administrator by using the KILL statement.
5) Compile errors, such as syntax errors, that prevent a batch from running.
6) Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

DMCA.com

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Most Imp SQL Concepts, SQL Server, SQL Server Exception handling and tagged . Bookmark the permalink.

31 Responses to Exception handling in SQL Server

  1. Thanks for sharing this article.
    I would have liked to see a business scenario where this kind of exception handling would be beneficial.
    We, application developer usually handle exceptions at the data access layer. We test the stored procedures against the expected data model and then integrate with the application.
    So the question here is where do you think this extra overhead would be useful, I mean the use cases?

  2. Pingback: love, music, song, songs, TagsForLikes, TagsForLikesApp, melody, hiphop, rnb, pop, rap, dubstep, instagood, beat, beats, party, partymusi, newsong, lovethissong, favoritesong, bestsong, hnhh, instahot, kinglbent

  3. Pingback: Dentist In Atlanta Georgia

  4. Pingback: GCLUB

  5. Pingback: mukapoker

  6. Pingback: www.roids4eu.com

  7. Pingback: https://www.scripde.com/java-error-could-not-find-or-load-main-class-solutions/

  8. Pingback: Top 5 richest men in the world

  9. Pingback: tor network wiki

  10. Pingback: Extreme Vehicle Creation

  11. Pingback: 카지노

  12. Pingback: Totojitu

  13. Pingback: 바카라사이트

  14. Pingback: go to the website

  15. Pingback: rajacapsa

  16. Pingback: marketing for dummies

  17. Pingback: get it now

  18. Pingback: 검증토토사이트

  19. Pingback: w88ok

  20. Pingback: How compression socks help

  21. Pingback: https://potamy.pl/

  22. Pingback: didi

  23. Pingback: scam

  24. Pingback: w88th

  25. Pingback: w88club

  26. Pingback: บริษัท ออล ครีเอตี้ แลนด์ จำกัด

  27. Pingback: Maui Potente

  28. Pingback: data sgp

  29. Pingback: Everything you Need to Know About Gold and Silver Investing

  30. Pingback: p2585#https://domowniczy.pl/forum/wokol-domu-f5/wakacje-2019-t259-s20.html#p2585

  31. Pingback: laguqq

Leave a Reply