calling a stored procedure from another on sql server 2000, bo

Here are two outstanding articles.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Alejandro Mesa" wrote:
> Is this case you do not need a transaction in the second sp, if the insert
> fails, nothing with be inserted. You have to check the return value of the
> second sp in the first one.
> CREATE PROCEDURE dbo.TestTran1
> as
> set nocount on
> declare @.rv int
> declare @.erri int
> begin tran
> insert into prosjekt values('this is a test', NULL, 1)
> select @.erri = @.@.error
> if @.erri <> 0
> goto errHandler
> exec @.rv = TestTran2
> select @.erri = coalesce(nullif(@.rv, 0), @.@.error)
> if @.erri <> 0
> goto errHandler2
> commit tran
> return 0
> errHandler:
> if @.@.trancount > 0
> rollback tran
> Raiserror('Det oppstod en feil i TestTran1',16,1)
> GO
> CREATE PROCEDURE dbo.TestTran2 AS
> set nocount on
> insert into prosjekt values('Dette er et testprosjekt Dette er et
> testprosjekt Dette er et testprosjekt ', null, 2)
> return @.@.error
> GO
>
> AMB
>
> "kongsballa" wrote:
>Thank you both for your replies!!
bd, I see your point there!
Alejandro, the reason both procedures use the "begin transaction" is
because they should also be able to run standalone. I have the same
problem with stored procedures in my production environment. Different
stored procedures might call each other to do a piece of work, but can
also be called standalone. Would you concider the approach where you
test the @.@.trancount first as a good one?
I will read the articles you recommended.
Thanks!!
Henning :-)
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!