Nested Stored Procs w/Transactions

I have some nested stored procedures where one sp calls another, etc. I nee
d
this wrapped in a transaction so that if an error occurs on any one sp
(either the calling sp or the one that is called) it will fail.
I'm continuall getting this error: Transaction count after EXECUTE
indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 2, current count = 3." So I've been playing around with
where to put the Begin Tran, Committ,
Rollback, etc.
Here's some pseudo code:
CREATE PROCEDURE [dbo].[spFILE_PROCESS]
AS
--Perform some queries, etc, then:
Exec spInsert_Customer
Exec spInsert_Trans
----
Where do I place Begin Tran/ committ, etc? I want both spInsert_Customer
and spInsert_Trans to be their own transaction as I call these sps by
themselves
elsewhere in my application.Hi,
have you tried this:
create procedure dbo.spFile_Process
as
set implicit_transactions off
--do something here without transaction
begin tran OuterTran
exec spInsert_Customer
exec spInsert_Trans
commit OuterTran
both nested procedures should have the same construction:
create procedure spInsert_Customer
as
set implicit_transactions off
begin tran TranA
...
commit TranA
create procedure spInsert_Trans
as
set implicit_transactions off
begin tran TranB
...
commit TranB
in case of error you should rollback the transation you are within
(decreasing @.@.trancount) and possible outer transactions. you have to
specify name of the transaction when rolling back, otherwise you will roll
back all transactions you're in.
HTH
Peter|||Alternately you could check for the existance of a transaction at the
begining of each procedure and only open a new transaction within the
procedure if one does not exist:
IF @.@.Trancount != 0
set @.Dotran = 0
...
If @.Dotran = 1
begin tran
...
-- on error
if @.dotran = 1
rollback transaction
return @.error
-- on success
if @.dotran = 1
comit tran
return 0
"Rogas69" wrote:

> Hi,
> have you tried this:
> create procedure dbo.spFile_Process
> as
> set implicit_transactions off
> --do something here without transaction
> begin tran OuterTran
> exec spInsert_Customer
> exec spInsert_Trans
> commit OuterTran
> both nested procedures should have the same construction:
> create procedure spInsert_Customer
> as
> set implicit_transactions off
> begin tran TranA
> ...
> commit TranA
> create procedure spInsert_Trans
> as
> set implicit_transactions off
> begin tran TranB
> ...
> commit TranB
> in case of error you should rollback the transation you are within
> (decreasing @.@.trancount) and possible outer transactions. you have to
> specify name of the transaction when rolling back, otherwise you will roll
> back all transactions you're in.
> HTH
> Peter
>
>|||Aren't you forgetting :
if @.@.Trancount = 0
set@.DoTran = 1
Anyway...
I've gone ahead and added the following whenever the committ tran, begin
tran, or rollback tran appear in my stored procs:
IF @.@.TRANCOUNT > 0 AND @.@.ERROR <> 0 BEGIN
ROLLBACK TRAN
RETURN
END
IF @.@.TRANCOUNT > 0 AND @.@.ERROR = 0 BEGIN
COMMIT TRAN
END
Now I'm longer getting the error message, but the Transaction is not being
rolled back either.
"Tony Sellars" wrote:
> Alternately you could check for the existance of a transaction at the
> begining of each procedure and only open a new transaction within the
> procedure if one does not exist:
> IF @.@.Trancount != 0
> set @.Dotran = 0
> ...
> If @.Dotran = 1
> begin tran
> ...
> -- on error
> if @.dotran = 1
> rollback transaction
> return @.error
> -- on success
> if @.dotran = 1
> comit tran
> return 0
>
> "Rogas69" wrote:
>

Nested Rollback/Commits Question

Hello all
I have a question regarding Rollbacks and Committs when
you are nesting stored procedures. My problem is that i
continue to get the following error.
Transaction count after EXECUTE indicates that a COMMIT
or ROLLBACK TRANSACTION statement is missing. Previous
count = 0, current count = 1
I am not sure what i am doing wrong. Any help would be
appriciated. Here is a sample SP that i may be nesting
in a larger SP.
---
CREATE PROCEDURE usp_Update_Client_Biography
@.Client_id as integer,
@.Biography as varchar(1000)
AS
SET NOCOUNT ON
DECLARE @.intErrorCode integer
SELECT @.intErrorCode = @.@.Error
IF @.intErrorCode = 0
BEGIN TRANSACTION
IF @.intErrorCode = 0
BEGIN
UPDATE Client_Biography
SET biography = @.Biography
WHERE biz_association_id = @.Client_id
SELECT @.intErrorCode = @.@.Error
END
IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
RETURN @.intErrorCode
THANKS
BAPerhaps @.@.TRANCOUNT is 1 when you enter the procedure? If you exit the proc with some other
trancount then when entering, you get such error. And remember that ROLLBACK exits the transaction
(@.@.TRANCOUNT to 0) and not only reduces the @.@.TRANCOUNT.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message news:065201c38109$f1f0e150$a001280a@.phx.gbl...
> Hello all
> I have a question regarding Rollbacks and Committs when
> you are nesting stored procedures. My problem is that i
> continue to get the following error.
> Transaction count after EXECUTE indicates that a COMMIT
> or ROLLBACK TRANSACTION statement is missing. Previous
> count = 0, current count = 1
> I am not sure what i am doing wrong. Any help would be
> appriciated. Here is a sample SP that i may be nesting
> in a larger SP.
> ---
> CREATE PROCEDURE usp_Update_Client_Biography
> @.Client_id as integer,
> @.Biography as varchar(1000)
> AS
> SET NOCOUNT ON
> DECLARE @.intErrorCode integer
> SELECT @.intErrorCode = @.@.Error
> IF @.intErrorCode = 0
> BEGIN TRANSACTION
> IF @.intErrorCode = 0
> BEGIN
> UPDATE Client_Biography
> SET biography = @.Biography
> WHERE biz_association_id = @.Client_id
> SELECT @.intErrorCode = @.@.Error
> END
>
> IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
> ELSE
> ROLLBACK TRANSACTION
>
> RETURN @.intErrorCode
>
> THANKS
> BA|||When you rollback a transaction it will rollback all the transactions that
it is nested in. So you won't have any open transaction by the time you get
to your errorhandler. You can catch this by changing your code (at the end)
to:
IF @.@.TRANCOUNT > 0
IF @.intErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
You might want to have a look at the SET XACT_ABORT option. If you have it
on any error will cause the transaction to roll back and prevent any further
code in the batch to execute. The disadvantage of this of course is that you
can't do any custom error handling, the advantage is that you don't have to
do any custom error handling.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message
news:065201c38109$f1f0e150$a001280a@.phx.gbl...
> Hello all
> I have a question regarding Rollbacks and Committs when
> you are nesting stored procedures. My problem is that i
> continue to get the following error.
> Transaction count after EXECUTE indicates that a COMMIT
> or ROLLBACK TRANSACTION statement is missing. Previous
> count = 0, current count = 1
> I am not sure what i am doing wrong. Any help would be
> appriciated. Here is a sample SP that i may be nesting
> in a larger SP.
> ---
> CREATE PROCEDURE usp_Update_Client_Biography
> @.Client_id as integer,
> @.Biography as varchar(1000)
> AS
> SET NOCOUNT ON
> DECLARE @.intErrorCode integer
> SELECT @.intErrorCode = @.@.Error
> IF @.intErrorCode = 0
> BEGIN TRANSACTION
> IF @.intErrorCode = 0
> BEGIN
> UPDATE Client_Biography
> SET biography = @.Biography
> WHERE biz_association_id = @.Client_id
> SELECT @.intErrorCode = @.@.Error
> END
>
> IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
> ELSE
> ROLLBACK TRANSACTION
>
> RETURN @.intErrorCode
>
> THANKS
> BA|||This is a multi-part message in MIME format.
--=_NextPart_000_009C_01C380EE.56F75F50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
declare
@.trancount int
set @.trancount =3D @.@.trancount
if @.trancount =3D 0
begin tran MyTran
else
save tran MyTran
-- do the work, check errors
if @.@.ERROR =3D 0
begin
if @.trancount =3D 0
commit tran
end
else
rollback tran MyTran
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"BA Baracus" <JCoxEUP@.hotmail.com> wrote in message =news:06ea01c3810f$02b36670$a001280a@.phx.gbl...
Thanks for the help!
Is there any way you can show me some T-SQL that will show me your suggestion? If i understand you correctly, i should check the transaction count before beginning the transaction? This has been an ongoing issue with me and i am very appriciative of your help!
thanks BA
>--Original Message--
>Perhaps @.@.TRANCOUNT is 1 when you enter the procedure? If you exit the proc with some other
>trancount then when entering, you get such error. And remember that ROLLBACK exits the transaction
>(@.@.TRANCOUNT to 0) and not only reduces the @.@.TRANCOUNT.
>-- >Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=3Ddjq&as=20
ugroup=3Dmicrosoft.public.sqlserver
>
>"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message news:065201c38109$f1f0e150$a001280a@.phx.gbl...
>> Hello all
>> I have a question regarding Rollbacks and Committs when
>> you are nesting stored procedures. My problem is that i
>> continue to get the following error.
>> Transaction count after EXECUTE indicates that a COMMIT
>> or ROLLBACK TRANSACTION statement is missing. Previous
>> count =3D 0, current count =3D 1
>> I am not sure what i am doing wrong. Any help would be
>> appriciated. Here is a sample SP that i may be nesting
>> in a larger SP.
>> ---
>> CREATE PROCEDURE usp_Update_Client_Biography
>> @.Client_id as integer,
>> @.Biography as varchar(1000)
>> AS
>> SET NOCOUNT ON
>> DECLARE @.intErrorCode integer
>> SELECT @.intErrorCode =3D @.@.Error
>> IF @.intErrorCode =3D 0
>> BEGIN TRANSACTION
>> IF @.intErrorCode =3D 0
>> BEGIN
>> UPDATE Client_Biography
>> SET biography =3D @.Biography
>> WHERE biz_association_id =3D @.Client_id
>> SELECT @.intErrorCode =3D @.@.Error
>> END
>>
>> IF @.intErrorCode =3D 0 AND @.@.TRANCOUNT > 0
>> COMMIT TRANSACTION
>> ELSE
>> ROLLBACK TRANSACTION
>>
>> RETURN @.intErrorCode
>>
>> THANKS
>> BA
>
>.
>
--=_NextPart_000_009C_01C380EE.56F75F50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
declare
@.trancount int
set @.trancount =3D =@.@.trancount
if @.trancount =3D =0
begin tran MyTran
else
save tran MyTran
-- do the work, check =errors
if @.@.ERROR =3D 0
begin
if =@.trancount =3D 0
= commit tran
end
else
rollback =tran MyTran
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"BA Baracus" wrote in =message news:06ea01c3810f$02=b36670$a001280a@.phx.gbl...Thanks for the help!Is there any way you can show me some T-SQL that =will show me your suggestion? If i understand you correctly, i =should check the transaction count before beginning the transaction? =This has been an ongoing issue with me and i am very appriciative of your help!thanks BA >--Original Message-->Perhaps @.@.TRANCOUNT is 1 when you enter the =procedure? If you exit the proc with some other>trancount then when =entering, you get such error. And remember that ROLLBACK exits the transaction>(@.@.TRANCOUNT to 0) and not only reduces the @.@.TRANCOUNT.>>-- >Tibor Karaszi, SQL Server MVP>Archive at: http://groups.google.com/groups?oi=3Ddjq&as">http://groups.go=ogle.com/groups?oi=3Ddjq&as ugroup=3Dmicrosoft.public.sqlserver>>>"B.A. =Baracus" =wrote in message news:065201c38109$f1=f0e150$a001280a@.phx.gbl...> Hello all>> I have a question regarding Rollbacks =and Committs when> you are nesting stored procedures. My =problem is that i> continue to get the following error.>> Transaction count after EXECUTE indicates =that a COMMIT> or ROLLBACK TRANSACTION statement is missing. Previous> count =3D 0, current count =3D =1>> I am not sure what i am doing wrong. Any help would be> appriciated. Here is a sample SP that i may be nesting> =in a larger SP.>> --->>= CREATE PROCEDURE usp_Update_Client_Biography> @.Client_id as integer,> @.Biography as varchar(1000)>> =AS>> SET NOCOUNT ON>> =DECLARE @.intErrorCode integer> SELECT @.intErrorCode =3D @.@.Error>> IF @.intErrorCode =3D 0> BEGIN =TRANSACTION>> IF @.intErrorCode =3D 0> BEGIN> UPDATE Client_Biography> SET biography =3D @.Biography> WHERE biz_association_id =3D @.Client_id>> SELECT @.intErrorCode =3D =@.@.Error> END>>> IF @.intErrorCode =3D 0 AND =@.@.TRANCOUNT > 0> COMMIT TRANSACTION> ELSE> =ROLLBACK TRANSACTION>>> RETURN @.intErrorCode>>> THANKS> BA>>>.>

---

--=_NextPart_000_009C_01C380EE.56F75F50--|||Hi.
I'm currently reading SQL Server 2000 Stored Proc 7 XML Programing,
2nd ed. By Dejan Sunderic - ISBN 0-07-222896-2
So far it has been an excellent book IMHO though I'm only on chapter 9
I have just read the chapter dealing with errorcodes & transactions,
using the information in the book I've got a template that I've
started to use for all my stored procs which might be of some use to
you.
What I would like is if any Guru out there can comment on the template
and let me know before I go to far with it if its as good as what the
book seems to be saying it is.
Thanks & HTH.
Al
/*
**
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
** Function : usp__Template
** Language : T-SQL
** Description : This is a template for all stored procs
** :
** Input : None
** Returns : None
**
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
** Ver Date Description of modification
**
---
** 1.0 date here text here
**
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
*/
CREATE PROCEDURE [dbo].[usp__Template]
-- @.intInValuesHere INT ,
-- @.intInValuesMoreHere INT ,
-- @.intOutputParam INT OUTPUT
AS
SET NOCOUNT ON
DECLARE @.intErrorCode INT , -- to store the errorcodes
@.intTranCountOnEntry INT , -- Store transaction count
@.intAnotherVariable INT
SELECT @.intErrorCode = @.@.Error ,
@.intTranCountOnEntry = @.@.TranCount ,
@.intAnotherVariable = 0
if @.@.TranCount = 0
BEGIN TRANSACTION
-- T-SQL code here
SELECT @.intErrorCode = @.@.Error
IF @.intErrorCode > 0 GOTO ERROR_HANDLER
-- more T-SQL code here
SELECT @.intErrorCode = @.@.Error
IF @.intErrorCode > 0 GOTO ERROR_HANDLER
IF @.@.TranCount > @.intTranCountOnEntry
COMMIT TRANSACTION
RETURN 0
/****************************************
******* ERROR HANDLER CODE BELOW ********
*****************************************/
ERROR_HANDLER:
IF @.@.TranCount > @.intTranCountOnEntry
ROLLBACK TRANSACTION
RAISERROR ('Some Error Message here...',16,1)
RETURN @.intErrorCode
GO
On Mon, 22 Sep 2003 06:03:41 -0700, "B.A. Baracus"
<JCoxEUP@.hotmail.com> wrote:
>Hello all
>I have a question regarding Rollbacks and Committs when
>you are nesting stored procedures. My problem is that i
>continue to get the following error.
>Transaction count after EXECUTE indicates that a COMMIT
>or ROLLBACK TRANSACTION statement is missing. Previous
>count = 0, current count = 1
>I am not sure what i am doing wrong. Any help would be
>appriciated. Here is a sample SP that i may be nesting
>in a larger SP.
>---
>CREATE PROCEDURE usp_Update_Client_Biography
> @.Client_id as integer,
> @.Biography as varchar(1000)
>AS
>SET NOCOUNT ON
>DECLARE @.intErrorCode integer
>SELECT @.intErrorCode = @.@.Error
>IF @.intErrorCode = 0
> BEGIN TRANSACTION
>IF @.intErrorCode = 0
> BEGIN
> UPDATE Client_Biography
> SET biography = @.Biography
> WHERE biz_association_id = @.Client_id
> SELECT @.intErrorCode = @.@.Error
> END
>
>IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
>ELSE
> ROLLBACK TRANSACTION
>
>RETURN @.intErrorCode
>
>THANKS
>BA

Nested execution of strored procedures

How i can capture one nested stored procedure's ouput parameter and using the out param in calling stored procedure...Help me..declare @.i int
exec sptest @.i output

create procedure sptest
@.i int output
as
...

For a dynamic sp call see
www.nigelrivett.com
setting variables from dynamic sql

Need to Subtract the result of two stored procedures

I am trying to take the results from two stored procedure and subtract them to get credit from two columns.

The stored procedures look like

create PROCEDURE mw AS
select *
FROM pla p
where p.st in ('ds', 'sd') and (iPlayerID = '5345')
compute sum(p.amount)
RETURN

both procedure have the same syntax. I am not able to use AS in to insert the values into a variable. Is there a wizard to use?

Ink:

My suggestion would be to transform your stored procedure into a function so that the results can be joined with each other.

Another thing: Try to avoid using SELECT * statements inside of stored procedures that target permanent tables. Such SELECT * statements leave landmines behind such that in the future the stored procedure may not function as intended. For stored procedures, the SELECT * is translated at compile time. When new columns are added to a table the meaning of SELECT * inside of a stored procedure does not automatically change when the table definition is changed. This can lead to bad surprises.

|||any suggestions on syntax for the funtion.|||

Please check under the "CREATE FUNCTION" article in books online. It should be something like this:

-- Table Function
create function dbo.fmw
( @.pm_iPlayerID varchar(10) -- Guessing at size
) -- You will need to make it right
returns @.outputTable table
( col_01 type_01,
...
col_N type_N
)
as
begin

insert into @.outputTable
select col_01,
col_02,
...
col_N
from pla
where p.st in ('ds', 'sd')
and iPlayer = @.pm_iPlayerID

return

end

go

-- Inline Function
create function dbo.fmw
( @.pm_iPlayerID varchar(10) -- Guessing at size
) -- You will need to make it right
returns table
as
return
( select col_01,
col_02,
...
col_N
from pla
where p.st in ('ds', 'sd')
and iPlayer = @.pm_iPlayerID
)

end

go

|||Also, it is in general preferable to use INLINE FUNCTIONS over table functions when possible.|||Thanks I will give it a try and keep you posted.|||I used the inline for one of the functions. Now do I call the returns from the stored procedure as parameters to the functions.|||Can you post your stored procedure and the inline function?|||

stored procedures are

--Money Played stored procedure
CREATE PROCEDURE MoneyPlayed AS
select *
FROM table s
where s.strStatus in ('GP', 'GB') and (id= '4134')
compute sum(s.iAmount)
RETURN

--Money won stored procedure
create PROCEDURE MoneyWon AS
select *
FROM table p
where p.strStatus in ('GP', 'GB') and (id= '4134')
compute sum(p.iAmount)
RETURN

_

create function dbo.played
( @.pm_iPlayerID int

)

returns table
as
return
( select ID as Player,
dbo.table.DevID as DeviD,
dbo.table.Seq as Seq,
dbo.table.CardID as CardiD,
dbo.table.CashBalance as cashBalance,
dbo.table.CredBalance as creditBalance,
dbo.table.FreePlay as freePlay,
dbo.table.Amount as Amount,
dbo.table.Status as Status,
dbo.tableDate as Date
from PlayerTab
where strStatus in ('bon', 'pla')
and iPlayerID = '7557'
)

|||

This looks like a good start.

Now, modify this line in your function:

and iPlayerID = '7557'

to:

and iPlayerId = @.pm_iPlayerID

Next, perform the same transformation on your moneyWon stored procedure and create a Won inline function. Once that is done you can then process the output from the two functions in a single SQL query. If what you are looking to do is to take the difference of the sums of the AMOUNT fields you can do something like:

select sum(w.amount) - p.amount
from played ('7557')
inner join
( select sum(mp.amount) as amount
from won ('7557') mp
) p

But this is speculation. First, another question. You had in your first query in which you were filtering for statuses 'GP' and 'GB'. Later you filtered for statuses 'bon' and 'play'. Can your status filter vary? If so, describe how status can vary. Will you always look for two of them? How many characters can the STATUS filter contain? How many characters can the PLAYER ID filter contain? Also, is PlayerID really an integer or a string? You defined your @.pm_iPlayerID parameter as an integer, yet you compare the iPlayerID column to a string. Is that an oversight?

|||Another thing is that it is beginning to look like maybe the best approach to solving this problem is with neither combining the results of functions nor combining the results of stored procedures. Do you want to continue with the function vein to answer your original question about combining results or do you want to diverge to perhaps a better way of solving this specific problem?|||

Yes it was an oversight to have the variables as different.

Thank you for your time

Steve Scheberle

|||

That worked for a bit then I noticed that I need to apply the value to create credit.

DECLARE @.credit money

SELECT @.credit= sum(amount)+(moneySpent-@.moneyWon)

From Table

Where status in ('var', 'var', 'var', 'var')

and id= '5455'

select @.credit

|||

Got it

Steve Scheberle