SQL Server - How to Run Scripts in a Transactional Way?

SQL Server 2000:

Approach1: Use @@ERROR

You have to repeat checking @@ERROR after each script:

BEGIN T1


INSERT INTO [ReportSystem] ([ReportSystemId], [ReportSystemName], [BaseReportUrl]) VALUES(2, 'QlikView', NULL);


IF (@@ERROR <> 0) GOTO DoRollBack


DELETE FROM ReportSystem


IF (@@ERROR <> 0) GOTO DoRollBack


DELETE FROM FilterParameterType


IF (@@ERROR <> 0) GOTO DoRollBack

COMMIT T1

RETURN

DoRollBack:
ROLLBACK TRAN T1
PRINT 'Rolled back'

Approach2: Use sp_executesql

BEGIN T1

declare @rc int;

declare @sql nvarchar(500);

set @sql = N'DELETE FROM FilterParameterType; DELETE FROM ReportSystem;';

exec @rc = sp_executesql @sql;

if @rc <> 0 ROLLBACK T1

COMMIT T1

SQL Server 2005:

Approach1: Use TRY...CATCH

http://msdn.microsoft.com/en-us/library/ms175976.aspx