Posted by Erland Sommarskog on 10/10/31 11:27

laurenq uantrell (laurenquantrell@hotmail.com) writes:
>>Look at it this way: you have this handling in many stored procedures.
>>Maybe you happen to forget to insert it in some place. And you have
>>this handling in many places in your client code. Maybe you acceidently
>>leave it in some place. So keep a double safeguard.
>
> I'm not sure what you'rer suggesting...
>
> I thought that this would clear up any error in the sproc:
>
> SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN
> @err END
>
>
> COMMIT TRANSACTION
> SELECT @err = @@error IF @err <> 0 RETURN @err
>
>
> Are you suggesting I put:
>
> IF @@trancount > 0 ROLLBACK TRANSACTION
>
> inthe sproc as well and not check for that in the front-end
> application?

So I'm suggesting that you should keep things as they are. (But make
sure that your transaction scope is the right one. The procedure
you posted seemed funny to me with a COMMIT in the middle.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]