Error control is important in database programmingsince it gives the ability to roll back the transactions in response to theproblems occurred.
By default, SQL server doesn’t stop transactions due toexceptions which can result invalid data. Error handling for TSQL is similar tothe exception handling in the java language. A group of TSQL statements can bewritten in a TRY block. If an error occurs TRY statement capture theexception. If exception occurs the tryblock the control is passed to CATCH block.
There are lot of benefits with TRY and CATCH blockslike Exceptions provide a clean way to check for errors without clutteringcode, exceptions can be seen by the programmer and checked during thecompilation process. Most of the people use try/catch block in stored procedureIn order to log any exceptions that occurs in database since it is good for theapplications which require integrity. Below I listed4 best practices while implementing the TRY CATCH BestPractice 1: Each TRY CATCH must be inside a single batch, stored procedure ortrigger. TRY block must be followed by a CATCH block.
TRY CATCHwill not handle the errors which cause the database engine to terminate. TRYCATCH does not work for compilation errors. Eg: create procedure selectdataASBEGIN BEGIN TRYSelect * from abcEND TRYBEGIN CATCHEND CATCHENDBestPractice 2: TRY CATCH BLOCK with ROLLBACK AND COMMIT Incomplete transactions shouldn’t be committed. If oneof the statements produce error than We can roll back the transaction and eraseall data starting from the transaction. If there is no error then we can COMMITthe transactions.Example of using TRY CATCH block in storedprocedure while modifying the data in the tables.Let us assume that I have a bank table and I want totransfer the money 5000 from account Ato another account B.
For this I have to first withdraw the amount from account A and credit it to the account Bso I have to write UPDATE account SETtotal=total+5000.0 WHERE account_id=1337;UPDATE account SETtotal=total-5000.0 WHERE account_id=45887;Thesetwo statements should execute at the same time i.e; both have to be performedor both should fail. If any of the one is succeed without other than data isnot correct. So for this we can use transaction and also a trycatch block for the commit and roll back Create procedure transfer(@accA int , @accB int)AsBEGIN TRY BEGIN TRANSACTION UPDATE account SETtotal=total+5000.
0 WHERE [email protected];UPDATE account SETtotal=total-5000.0 WHERE [email protected]; –if there is no errorCOMMITEND TRY BEGIN CATCH ROLLBACKPrint ‘Cannot done the transaction ‘END CATCH ENDWhen we execute this procedure if the two commandsexecute successful then transaction Commit else it goes to CATCH block andperforms rollback then print cannot done the transaction. BESTPRACTICE 3: USING TRY-CATCH with XACT_ABORTFor good error handling we need both TRY-CATCH and SETXACT_ABORT ON.
The option XACT_ABORT is needed for more reliable error andtransaction handling. SET XACT_ABORT ON instructs SQL SERVER to roll back theentire transaction and abort the batch when a runtime error occurs. It also specifies what action SQL SERVER should talefollowing run-time errors.SET XACT_ABORT OFF is the default one it indicatedthat only the T-SQL statement which raised the error is rollback and thetransaction continues. BESTPRACTICE 4: USING TRYCATCH with RAISEERROR OR THROWRAISEERROR and THROW statements are used to raise anerror in SQL SERVER. THROW statement iseasy to use compared to RAISE ERROR.
BOTH can be used in CATCH block to raise the error. By just specifyingthe THROW statement without any parameters and RAISEERROR with parameters. IFwe want to raise even system exception it is better to use RAISEERROR insteadof THROW. Example: USING TRY CATCH USING THROW Let us assume that I have two tables, employee and employeephonenosboth are related to each other and I have procedure called Deleteemployee. The delete employee has 2 statements todelete records in employeephonenumbers which are related to employee table.Here suppose if we get the error in deleting therecords we can handle the situation using the try catch and throwstatements Create procedure deteleemployee(@employeeid int)AsBEGIN TRY BEGIN TRANSACTION Delete from employeephonenosWhere employeeid= @employeeid Delete from employeeWhere employeeid= @employeeid –if there is no errorCOMMITEND TRY BEGIN CATCH ROLLBACKPrint ‘Any message or transaction rollback’THROWEND CATCH END In the Try block atransaction is started and the two DELETE statements are performed.
IF both aresucceed then COMMIT will be executed and the transaction will be committed. If, either one of the delete statementgives an error then control goes to the CATCH block .In the catch block thetransaction will be roll back and we will get the message transaction rollback and the Throw gives the messagewhat type of error it is