Wednesday, April 8, 2015

What are ROLLBACK WORK AND COMMIT WORK?

I am sure that you are familiar with ROLLBACK TRANSACTION and COMMIT TRANSACTION but have you heard about or used ROLLBACK WORK and COMMIT WORK?

ROLLBACK WORK and COMMIT WORK work exactly same way as ROLLBACK TRANSACTION and COMMIT TRANSACTION. WORK keyword is optional and this is ISO-compatible.

Can we use this instead what we have been used? Yea it is possible and no harm at all. Only missing part is, this does not accept user-defined transaction name.

Here is a sample code using ROLLBACK WORK and COMMIT WORK.

USE AdventureWorks2014;
GO

BEGIN TRAN

UPDATE Production.Product
 SET Color = 'b'
WHERE Color = 'Black';

-- simple logic to test
IF @@ROWCOUNT > 10
BEGIN

 ROLLBACK WORK;
 PRINT 'transaction rolled back';
END
ELSE
BEGIN
 
 COMMIT WORK;
 PRINT 'transaction committed.';
END

No comments: