Commitment Control in ad-hoc SQL queries

Commitment control can be implemented using TRANSACTION statements. An example is shown below.

BEGIN TRANSACTION

UPDATE tblUser SET sName=sName+’@’ WHERE sName like ‘F%’

IF @@ERROR<>0
BEGIN
   ROLLBACK TRANSACTION
END
ELSE
BEGIN
   COMMIT TRANSACTION
END
GO

When using query analyser, commitment control can be applied across a series of individually run SQL statements, see the example below.

BEGIN TRANSACTION

USE pubs
GO

UPDATE titles
SET advance = advance * 1.25
WHERE ytd_sales > 8000
GO

COMMIT TRANSACTION
GO

Note: the BEGIN TRANSACTION statement will lock resource until either a COMMIT or ROLLBACK statement is executed.