SQL Server: Exception or Error Handling in SQL Server

Error handling in SQL Server gives us control over the Transact-SQL code. For example, when things go wrong, we get a chance to do something about it and possibly make it right again.

SQL Syntax:

BEGIN TRY

--code to try

END TRY

BEGIN CATCH

--code to run if an error occurs

--is generated in try

END CATCH

We have access to some special data only available inside the CATCH statement:

ERROR_NUMBER – Returns the internal number of the error
ERROR_STATE – Returns the information about the source
ERROR_SEVERITY – Returns the information about anything from informational errors to errors user of DBA can fix, etc.
ERROR_LINE – Returns the line number at which an error happened on
ERROR_PROCEDURE – Returns the name of the stored procedure or function
ERROR_MESSAGE – Returns the most essential information and that is the message text of the error

Error Handling Example:

if everything executes successfully inside the Begin transaction, it will insert a record into Sales, and then it will commit it. But if something goes wrong before the commit takes place and it transfers control down to Catch.

If the error isn’t serious, and it is in the committable state, we can still commit the transaction. But if something went wrong and is in an uncommittable state, then we can roll back the transaction. This can be done by simply running and analyzing the XACT_STATE function that reports transaction state.

This function returns one of the following three values:

1 – the transaction is committable
-1 – the transaction is uncommittable and should be rolled back
0 – there are no pending transactions

Example of Error Handling

begin try

insert into employee (id,name,dept,salary,state)

values(33,'yasmin','it',60000,'karnatka')

end try

BEGIN CATCH

SELECT ERROR_MESSAGE() AS [Error Message]

,ERROR_LINE() AS ErrorLine

,ERROR_NUMBER() AS [Error Number]

,ERROR_SEVERITY() AS [Error Severity]

,ERROR_STATE() AS [Error State]

END CATCH