How do you control transaction behavior in stored procedures?

In stored procedures, you control transaction behavior using the following:

• BEGIN TRANSACTION: Explicitly starts a transaction in the stored procedure.
• COMMIT: Ends the transaction and commits all changes made during the

transaction.

• ROLLBACK: Undoes all changes made during the transaction if something goes

wrong.

• SAVEPOINT: Sets a point in the transaction to which you can roll back.

Example:

BEGIN TRANSACTION;

• - SQL operations here

IF (some_condition)

BEGIN

COMMIT; -- Commit if condition is true

END

ELSE

BEGIN

ROLLBACK; -- Rollback if condition is false

END

Normalization & Database Design