Error control is important in database programming since it gives theability to roll back the transactions inresponse to the problems occurred. By default, SQL server doesn’t stoptransactions due to exceptions which can result invalid data. Error handling for TSQL is similar to the exception handlingin the java language. Previous versions of SQL Server 2005 RAISEERROR is used.Starting with SQL Server 2005 we can capture and handle exceptions using twostatements TRY and CATCH.
A group of TSQL statements can be written in a TRY block. Ifan error occurs TRY statement capture the exception. If exception occurs the try block the controlis passed to CATCH block.There are lot of benefits with TRY and CATCH blocks likeExceptions provide a clean way to check for errors without cluttering code,exceptions can be seen by the programmer and checked during the compilationprocess.The Throw statement is introduced in SQL SERVER 2012.
ThisThrow statement behaves similarly to RAISEERROR statement. Each TRY CATCH must be inside a single batch, storedprocedure or trigger. 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.Example of using TRYCATCH in procedure for deleting therecords.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.Eg:Create procedure deteleemployee(@employeeid int)AsDelete from employeephonenosWhere employeeid= @employeeid Delete from employeeWhere employeeid= @employeeid From the above procedure we want to statement to be eitherfail or both should be succeed so weneed to wrap both statements into transaction Create procedure deteleemployee(@employeeid int)AsBEGIN TRANSACTION Delete from employeephonenosWhere employeeid= @employeeid Delete from employeeWhere employeeid= @employeeid END Here suppose if we get the error in deleting the records wecan handle the situation using the try catch and throw statements 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 a transaction is started and the two DELETEstatements are performed.
IF both are succeed then COMMIT will be executed andthe transaction will be committed. If , either one of the delete statement gives an error thencontrol goes to the CATCH block .In the catch block the transaction will beroll back and we will get the messagetransaction rollback and the Throw gives the message what type of error it is.Example of using TRY CATCH block in stored procedurewhile 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 accountA and credit it to the account B so I have to write UPDATE account SETtotal=total+5000.0 WHERE account_id=1337;UPDATE account SETtotal=total-5000.
0 WHERE account_id=45887;These two statements should execute at the same time i.e; both have tobe performed or both should fail. If any of the one is succeed without otherthan data is not correct. So for this we can use transaction and also a try catchblock for the commit and roll back Create procedure transfer(@accA int , @accB int)AsBEGIN TRY BEGIN TRANSACTION UPDATE account SETtotal=total+5000.0 WHERE account_id=@accB;UPDATE account SETtotal=total-5000.
0 WHERE account_id=@accA; –if there is no errorCOMMITEND TRY BEGIN CATCH ROLLBACKPrint ‘Cannot done the transaction ‘THROWEND CATCH END When we execute this procedure if the two commands executesuccessful then transaction Commit else it goes to CATCH block and performsrollback then print cannot done the transaction along with the message from theTHROW command.In this way TRY and CATCH block is used for error handlingin SQL SERVER.
