Posted by NickName on 09/30/98 11:28

Let's say I may possibly use two transactions in a script, the second
one will depend on the successful execution of the first one.

The following code works. However, I'm wondering if SQL Server 2000
has some internal function like @@transaction_status to indicate the
status of the most recent transaction by the connection. The analogue
of @@FETCH_STATUS. Then, my own error tracking code could be omitted.

Thanks.

-- ENV: SQL Server 2000
-- ddls
create table tblA (col1 smallint, col2 smallint)
create table tblB (col1 smallint, col2 smallint)
create table tblX (col1 char(1), col2 varchar(20))

declare @errorCode tinyInt
set @errorCode = 0

begin transaction fTran
insert into tblA
values (7,1);

insert into tblB
values (8,0);

-- we know this guy will fail
insert into tblX
values ('ab','abcdefge')

If (@@error <> 0)
begin
select @errorCode = 1
end

if (@errorCode = 1)
rollback transaction fTran
else
commit transaction fTran

if (@errorCode = 0)
pseducode: start second transaction here ...
else
print 'fTran failed.';
RETURN

[Back to original message]