Transaction and Stored Procedure in SqlServer

Hi Michell,

You use TRANSACTIONS when you want to explicitly COMMIT or ROLLBACK your activity whereas stored procedure does explicit commits and does not perform in a group unless specified. For example if following statement is in the stored procedure:

INSERT INTO TABLEA;
UPDATE TABLEB;

and if UPDATE fails than the INSERT into TABLEA will be commited in the database and if you wrap this thing in the TRANSACTION like below:

BEGIN TRANS
INSERT INTO TABLEA;
UPDATE TABLEB;
COMMIT;

It will rollback if line 2 UPDATE fails.


Please do not forget to mark "Accepted Answer".