SEO

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

On Mar 18, 2:49 pm, cricket...@gmail.com wrote:
> 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

Your message is not clear about what error you have . If you are
referring to a table or column which is not in the database, no
rollback happens .

You can open a trasaction in one SP and call another SP . But make
sure that transaction should be as short as possible from execution
point of view .
You may encounter error in any one of the SPs at any time, you should
check for @@trancount > 0 before executing COMMIT OR ROLLBACK
statements in both the SPs.