Jag vill avbryta min transaktion om några fel uppstår. Finns det något smidigare sätt en att kontrollera @@ERROR efter varje sats?

BEGIN TRANSACTION
EXEC dbo.spUpdateStageGroup '{8A949ED0-1D99-4EB7-A201-7C7A6BD9429B}', Null, '{3FBFEC4D-4093-11D5-BC60-00A024419708}', '{2152A647-EE12-4C7F-B93E-01ECD2563A86}', 1
IF @@ERROR <> 0 GOTO BATCH_ERROR

EXEC dbo.spUpdateStageGroup '{5E8D0274-8EFE-4BAA-86AD-4C4039E35BF1}', '{8A949ED0-1D99-4EB7-A201-7C7A6BD9429B}', '{3FBFEC4D-4093-11D5-BC60-00A024419708}', '{5B50534B-B0D2-4AAC-A853-71ECF1F18F5B}', 2
IF @@ERROR <> 0 GOTO BATCH_ERROR

EXEC dbo.spUpdateStageGroup '{76E1613C-5177-4C73-8596-396FDCB883A7}', '{8A949ED0-1D99-4EB7-A201-7C7A6BD9429B}', '{3FBFEC4D-4093-11D5-BC60-00A024419708}', '{5B50534B-B0D2-4AAC-A853-71ECF1F18F5B}', 3
IF @@ERROR <> 0 GOTO BATCH_ERROR

EXEC dbo.spInsertStageGroup '{4AFFAFE1-2A5C-4F14-9E34-5A55654819C4}', '{8A949ED0-1D99-4EB7-A201-7C7A6BD9429B}', '{3FBFEC4D-4093-11D5-BC60-00A024419708}', '{BDB54DE4-3A39-4678-96AB-90C788CF2A6D}', 4
IF @@ERROR <> 0 GOTO BATCH_ERROR

EXEC dbo.spInsertStageGroup '{BCD5CE5B-FA1C-4BF7-A325-A5F27ACEEEED}', '{8A949ED0-1D99-4EB7-A201-7C7A6BD9429B}', '{3FBFEC4D-4093-11D5-BC60-00A024419708}', '{2152A647-EE12-4C7F-B93E-01ECD2563A86}', 5
IF @@ERROR <> 0 GOTO BATCH_ERROR

EXEC dbo.spDeleteStageGroup '{77D1A381-EA36-461B-A733-2156CA3CD0A7}'
IF @@ERROR <> 0 GOTO BATCH_ERROR

EXEC dbo.spDeleteStageGroup '{999734D9-F67D-446E-9515-9FE597959078}'
IF @@ERROR <> 0 GOTO BATCH_ERROR


COMMIT TRANSACTION
GOTO BATCH_EXIT

BATCH_ERROR:
ROLLBACK TRANSACTION

BATCH_EXIT:

Nej, inte vad jag kan komma på. @@error nollställs ju så fort man kör ett nytt statement så det går inte att vänta och kolla den senare. Ett alternativ kan vara att försöka göra ROLLBACK på transaktionen inifrån de procedurer du anropar. Jag har försökt testa detta, men kommer inte undan att jag får några varningar. Det fungerar dock som tänkt, men bör nog testas mer ändå. Säg till om du kommer fram till något.


create table foobar (a int)
go

create procedure bar
(@a int)
as
begin

if @a < 10
begin
print 'rollback görs nu...'
rollback tran mytran
end
else
begin
print 'ok'
end

end
go

create procedure foo
(@b int)
as
begin

begin tran mytran

insert into foobar values (@b)

exec bar @a=@b

commit tran mytran
end
go


-- Testa...
/*
exec foo 8

select @@trancount

select * from foobar
*/

Jag tror jag hittade det jag sökte:

Errors During Transaction Processing

If a severe error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to SQL Server is broken, any outstanding transactions for the connection are rolled back when the network notifies SQL Server of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and SQL Server rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.

If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

It is the responsibility of the programmer to code the application to specify the correct action (COMMIT or ROLLBACK) if a run-time or compile error occurs.