Transaction and Stored Procedure in SqlServer

Question

Explain difference between Transaction and Stored Procedure ?

Javeed M Shaikh · Answer

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".

Prabhu Raja · Answer

Hi, A transaction is a logical unit of work that contains one or more SQL statements.A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued. Stored procedures contains one or more SQL statements that are precomplied. This is simliar to functions or procedures in any programming languages .

Transaction and Stored Procedure in SqlServer

Answers (2)