Use of ROLLBACK in SQL server Stored Procedure


Wednesday, November 17, 2010

Use of ROLLBACK in SQL server Stored Procedure

You might have heard about ROLLBACK but you have never used it right ?

If you have used it, its well and good but if Not, then this blog might help you to know, how you can use ROLLBACK in your SP.

Suppose you want to use two insert statement, one by one.

You want to use, @@identity of first insert in another insert statement then this is the best method to go with.

Suppose you are doing first insert operation and some problem occure and any how you are not able to insert data in second table then there might be some database mapping issue.

so here, ROLLBACK can help you to avoid such database problem,

For example, you have used ROLLBACK in your SP, and you are doing first insert operation and if some problem occur and you are not able to insert in second table then ROLLBACk will undo your first insert operation also which will help to avoid database mapping issue.


Below is the syntax of ROLLBACK,


BEGIN TRY
    BEGIN TRAN
    BEGIN
        'You insert statement
                            
        COMMIT TRAN
                    
    END
END TRY
 
BEGIN CATCH
    BEGIN
        ROLLBACK TRAN
                    
        PRINT ERROR_MESSAGE()
    END
END CATCH




Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Senior Asp.Net Developer

Email: raj143svmit@gmail.com
e-Procurement Technologies Ltd (India)
www.abcprocure.com


No comments :