Posted by M A Srinivas on 03/19/07 05:12

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.

[Back to original message]