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.