Returning errors from a stored procedure using transactions

I'm having a brain cramp.
I'm writing a stored procedure that will do an insert into two tables in a
parent-child relationship. I want either both inserts to succeed or both to
fail. Obviously, a transaction is required. If an error occurrs any time
during the SP execution, I want to return the error to the application in th
e
same manner as SQL Server would if I wasn't using a transaction. So, my SP
looks something like:
DECLARE @.TheError INT
BEGIN TRAN
INSERT Parent...
SET @.TheError = @.@.ERROR
IF @.TheError = 0
BEGIN
INSERT Child...
SET @.TheError = @.@.ERROR
END
IF @.TheError = 0
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
END
If I Rollback the transaction, how do I return the error to the application
such that the CLR will generate the appropriate SQL Exception in my
application?
BOL wasn't clear about this.
Thanks,
BobThe original error will be returned to the application no matter what you
do. You can't _trap_ errors in SQL Server 2000, only handle them.
If you want to have really short code and get the behaviour you want, you
can use:
SET XACT_ABORT ON
BEGIN TRAN
INSERT Parent...
INSERT Child...
COMMIT TRAN
If there is an error, the transaction will automatically be aborted and
rolled back, and the error is still raised to the application. Note that no
further code will be executed after the statement where the error happens.
Jacco Schalkwijk
SQL Server MVP
"Bob" <notrainsley@.worldsavings.com> wrote in message
news:920AF55A-7551-4B05-AE55-C77D49D35391@.microsoft.com...
> I'm having a brain cramp.
> I'm writing a stored procedure that will do an insert into two tables in a
> parent-child relationship. I want either both inserts to succeed or both
> to
> fail. Obviously, a transaction is required. If an error occurrs any time
> during the SP execution, I want to return the error to the application in
> the
> same manner as SQL Server would if I wasn't using a transaction. So, my SP
> looks something like:
> DECLARE @.TheError INT
> BEGIN TRAN
> INSERT Parent...
> SET @.TheError = @.@.ERROR
> IF @.TheError = 0
> BEGIN
> INSERT Child...
> SET @.TheError = @.@.ERROR
> END
> IF @.TheError = 0
> BEGIN
> COMMIT TRAN
> END
> ELSE
> BEGIN
> ROLLBACK TRAN
> END
> If I Rollback the transaction, how do I return the error to the
> application
> such that the CLR will generate the appropriate SQL Exception in my
> application?
> BOL wasn't clear about this.
> Thanks,
> Bob
>|||Jacco,
Thank you. I vaguely remembered something like that, but since BOL didn't
explicitly say that, I wasn't sure. I'm glad I asked because I had completel
y
forgotten about XACT_ABORT.
To maximize reuseability, I usually write my SPs to do insert/update
operations on a single table and let my application code handle the
transactions, but in this case, a parent without a child row is not valid
from a business perspective, so I decided to let the SP do both inserts.
Bob
"Jacco Schalkwijk" wrote:

> The original error will be returned to the application no matter what you
> do. You can't _trap_ errors in SQL Server 2000, only handle them.
> If you want to have really short code and get the behaviour you want, you
> can use:
> SET XACT_ABORT ON
> BEGIN TRAN
> INSERT Parent...
> INSERT Child...
> COMMIT TRAN
> If there is an error, the transaction will automatically be aborted and
> rolled back, and the error is still raised to the application. Note that n
o
> further code will be executed after the statement where the error happens.
>
> --
> Jacco Schalkwijk
> SQL Server MVP