Rollback in Stored Procedure

Question

How to Rollback a transaction in Stored Procedure?

Shankar M · Answer

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable{mso-style-name:"Table Normal";mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-priority:99;mso-style-qformat:yes;mso-style-parent:"";mso-padding-alt:0in 5.4pt 0in 5.4pt;mso-para-margin-top:0in;mso-para-margin-right:0in;mso-para-margin-bottom:10.0pt;mso-para-margin-left:0in;line-height:115%;mso-pagination:widow-orphan;font-size:11.0pt;font-family:"Calibri","sans-serif";mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;} Hi Swathi, The Stored Procedure should have a BEGIN TRANSACTION, from where the transaction starts. You can check for @@ERROR or TRY..CATCH for Exception handler to handle the errors. If an error is occured, the Transaction can be rolled back using ROLLBACK statment. If you have two insert statements, if you are to use @@ERROR variable to check the error then, @@ERROR has to be checked twice for each Insert statments like, INSERT INTO EMP(EMPNO,DEPT) VALUES(1234,20); IF @@error 0 BEGIN ROLLBACK RAISERROR ('Error Inserting 1', 16, 1) RETURN END INSERT INTO DEPT(DEPTNO)VALUES(20); IF @@error 0 BEGIN ROLLBACK RAISERROR ('Error Inserting 2', 16, 1) RETURN END So, the @ERROR is variable is not reset for either the statement goes in to error.. So as to preserve the Atomicity of a Transaction. To avoid this writing lengthy code of checking @@ERROR variable for each statement, you can use TRY..CATCH block.

Pradip Pandey · Answer

Hi Swati Please refer this link. http://www.c-sharpcorner.com/Blogs/5318/ Hope it will help.