Posted by cricketweb on 03/18/07 09:49

I have a stored procedure that calls another stored procedure with the
first stored procedure opening a transaction:

BEGIN
SET XACT_ABORT ON

BEGIN TRANSACTION

does various updates/inserts

calls 2nd stored procedure to proccess updates/inserts common to many
other stored procedures

does more various updates/inserts

commit

END

The problem I'm having is that within the 2nd stored procedure is that
if it encounters an error, it does not roll back the entire
transaction and I finish up with missing records in the database. Am
using this in the 2nd stored procedure:

if(@TypeId1 = @TypeId2 and @Line1 <> '' and @Line2 <> '')
begin
RAISERROR('error message', 16, 1)
RETURN
end

What could the problem be? From what I've read, it seems as though
you can't have an open transaction within one sp that calls another sp
and it maintains the same transactoin? Is this corrrect?

I tired the following too, and I still couldn't get it to work. Any
ideas anyone?

************ sp 1 ***********

Declare @AddressError char(3)

SET XACT_ABORT ON
BEGIN TRANSACTION

exec Sp2
@AddressError OUTPUT,
@variable1,
@variable2,
etc. etc

************** sp 2 *****************

@AddressError char(3) OUTPUT,

if(@TypeId1 = @TypeId2 and @Line1 <> '' and @Line2 <> '')
begin
RAISERROR('error message', 16, 1)
RETURN
end

SET XACT_ABORT ON
BEGIN TRANSACTION

process updates/inserts

Set @AddressError = 'no'
Commit

******** back to sp 1************

If @AddressError <> 'no'
BEGIN
rollback transaction
END

continue doing updates/inserts

commit

[Back to original message]