calling a stored procedure from another on sql server 2000, both using transactions

Hi!
I am having some trouble. I have two stored procedures:
CREATE PROCEDURE dbo.TestTran1
as
declare @.erri int
begin tran
insert into prosjekt values('this is a test', NULL, 1)
select @.erri = @.@.error
if @.erri <> 0
goto errHandler
exec TestTran2
select @.erri = @.@.error
if @.erri <> 0
begin
goto errHandler2
end
commit tran
return 0
errHandler:
if @.@.trancount > 0
rollback tran
Raiserror('Det oppstod en feil i TestTran1',16,1)
GO
Here is procedure number 2:
CREATE PROCEDURE dbo.TestTran2 AS
begin tran
print 'in proc 2'
insert into prosjekt values('Dette er et testprosjekt Dette er et
testprosjekt Dette er et testprosjekt ', null, 2)
if @.@.error <> 0
begin
goto errH
end
commit tran
return 0
errH:
rollback tran
print 'rolling back'
raiserror('Det oppstod en feil i TestTran2', 16, 1)
GO
The first procedure inserts values to a testtable with success. Then it
calls the second procedure that will generate an error because it tries
to insert too much text in a varchar(50) column. What I am trying to
achieve here is to be able to use both procedures by it self and also be
able to call one from the other if nessesairy.
If I run the first procedure (that calls the other), I get the error:
"Server: Msg 266, Level 16, State 2, Procedure TestTran2, Line 26
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count =
0."
If I run the second procedure or the first one without calling the
second, everything works fine. The same goes if I make the second
procedure not fail when called from the first.
I have so far learned that an inner commit will not actually commit
anything, rather just decrement the @.@.trancount by one. The outer commit
will make the actuall commit. A rollback on the other hand will allways
make a rollback and set @.@.trancount = 0.
1. What am I doing wrong. Can I not use begin tran in two different
procedures calling each other?
2. Is there any need for using the begin tran expression? or will sql
server still handle everything like a transaction what ever one does?
Hope I made my self clear on the matter. Thanks!
Henning :-)
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi Henning.
What I've been doing, don't know if it's the best method or not, is
in each proc, test if it has been called within a transaction and if
not, use transactions. Also, to ensure your transaction isn't
automatically aborted upon a SQL error, use SET XACT_ABORT OFF
Example:
create procedure myProc
as
set xact_abort off
declare @.InTran tinyint
set @.InTran = @.@.trancount
if @.InTran = 0
begin
begin tran
end
-- do some stuff
if @.@.error <> 0
begin
if @.InTran = 0
begin
rollback tran
end
end
else
begin
if @.InTran = 0
begin
commit tran
end
end
Hope this helps. I look forward to anyone else's feedback.
Bryce