How to use try catch in SQL Server for error handling. What are the benefits?

Question

I would like to understand how to do error handling in SQL Server using try and catch. Is there any benefit by using that except the new way of catching the errors? How best how I can use it?

Answer 1

If you are familiar with try catch in C# or VB.Net, then it is the same way as that. Try catch is used to catch the excption.

One thing you may want to know in TSQL, when you execute any query, that is nothing but a transaction. So, each query will be considered as one transaction, and the result of that transaction will updating some global variable called @@ERROR. So, this variable will update right after every TSQL query execution. If @@Error <> 0, then the last execute TSQL query was having some issue and the query execution was not success.

INSERT INTO CUSTOMER VALUES(1, 'Testname')
IF @@ERROR <> 0
PRINT 'There were some error'

If there are more than one TSQL query has to be executed withing a single transaction, that is the situation we use TRAN.

BEGIN TRAN
INSERT INTO Customer VALUES(1, 'Name 1')
IF(@@ERROR <> 0) GOTO Error
INSERT INTO Customer VALUES(2, 'Name 2')
IF(@@ERROR <> 0) GOTO Error
COMMIT TRAN
GOTO Next
Error:
raiserror('Something wrong during query execution', 14, 18)
Next:

If you look at the above transaction, @@Error is being used after each TSQL query. Otherwise the value in the @@Error will be replaced by the next query execution.

Another thing has to be noted on the above transaction is, I have not called Commit or Roolback TRAN where there is an error. So, the transaction is left just like that and this kind of activity may lock down the table. No one may able to use it until we release the transaction.

Try Catch is the better option to resolve the above mentioned issues.

Whenever there is an error, it will automatically pass the control flow to Catch Block, also, there are some built in method we can use to get more information about the error. If there are any error in the transaction and if tansaction is inside a Try Catch block, then the transaction will be automatically rolled back if we have not implemented on catch block.

BEGIN TRY
BEGIN TRAN
INSERT INTO Customer VALUES(1, 'Name 1')
INSERT INTO Customer VALUES(2, 'Name 2')
COMMIT TRAN
END TRY
BEGIN CATCH
raiseerror('Something wrong during query execution', 14, 18)
END CATCH

As I told, there are some built in functions may give as more information about the error if we need to send it back to client or we need to use it in server side itself.

ERRORNUMBER() --> Returns the error number. For each error message in SQL, we have error number.
ERRORMESSAGE() --> returns the error message.
ERRORSEVERITY --> return the severity of the error.Severity 10 or less is considered as warning
ERRORLINE() --> Return the line number at which the error occurred
ERROR_PROCEDURE --> Returns name of the stored procedure or function from where query is exist.

For more information, you can refer below link

http://msdn.microsoft.com/en-us/library/ms179296(v=sql.105).aspx