Rollback in Stored Procedure

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.

Hi Swati

Please refer this link.

http://www.c-sharpcorner.com/Blogs/5318/

Hope it will help.