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 stored procs

Hi Guys, is there a way of nesting a stored procedure within another...i dont mean calling one within another...more like

creating one within another e.g

Create proc TEST1

as

Create proc TEST2

as

select 'this is inner proc'

where by running test one creates test2

i tried this and got an error:

Msg 156, Level 15, State 1, Procedure TEST1, Line 3

Incorrect syntax near the keyword 'proc'.

is this possible if so how, cos i am trying to create a master Proc that creates a DB, and Tables and store procs, hence i want to use it like a Batch file or script.

To nest stored procedures, you must create them separately and have one proc call the other -- maybe something like this:

create procedure A

as

print 'This is procedure a.'

go

create procedure B

as

exec A

go

exec B

-- - Output: -
-- This is procedure a.

If you are trying to create a procedure that creates other procedures, you will need to take a different approach. Stored procedures cannot directly create other database objects.

|||

You cannot create a stored procedure from within a stored procedure.

You can create a T-SQL script file, and then execute that script file from the command line using OSQL.exe or SQLCommand.exe

Refer to Books Online for OSQL utility, or SQLCommand.

|||Thanks Kent yeah i do now this form of nesting but its not suitable as i am not calling a proc from a proc|||

thanks Arnie, yes that was my second option to basically i wanted a stored proc to create a database i could pass parameters for the dbase name and a time stamp and then create stored procs, tables and inserts. but i guess i can do it 2 step : 1 proc i script

ta.

|||

One thing to remember is that when using scripts, each use of 'GO' will clear all variables. You may have to re-establish them for the next object.

Sometimes, from the stored procedure, I have loaded a [Name-Value] table with input parameter values that will be used as variables in the script file. The values can be pulled whenever needed.

|||

>> thanks Arnie, yes that was my second option to basically i wanted a stored proc to create a database i could pass parameters for the dbase name and a time stamp and then create stored procs, tables and inserts. but i guess i can do it 2 step : 1 proc i script

You would have to create the SPs in the newly created database so your SP create statements couldn't be in-line anyway.

Usually I do this via osql or just concatenated scripts.

|||

One other option would be to use dynamic sql to create the procedure. The code below in option 1 creates a permanent procedure that is then executed. The code in option 2 creates a temporary stored procedure that is then executed. Temporary procedures are nice when the task you are executing doesn't need to be permanent. HTH.

-Chris

--Option 1

create procedure myProc1 as

BEGIN

EXEC ('CREATE PROCEDURE myProc2 AS select getdate()')

exec myProc2

END;

GO

EXEC myProc1

--Option 2

create procedure myProc1 as

BEGIN

EXEC ('CREATE PROCEDURE #myProc2 AS select getdate()')

exec #myProc2

END;

GO

EXEC myProc1

|||

Why Not..! Yes you can create... But it is bit different....

You have to use the procedure numbers...

The main storedproc will be visible to world rest will be hidden.

You can call them from your main proc / externally..

Example:

Code Snippet

Create proc MyGroupedProc

(

@.Param as int

)

as

Begin

Exec MyGroupedProc;2 @.Param

End

Go

Create Proc MyGroupedProc;2

(

@.Param as int

)

as

Begin

Print 'This is Inner Proc [2]'

Select @.Param as [@. 2]

Exec MyGroupedProc;3 @.param

End

Go

Create Proc MyGroupedProc;3

(

@.Param as int

)

as

Begin

Print 'This is Inner Proc [3]'

Select @.Param as [@. 3]

End

Go

ExecMyGroupedProc 1

Select * from Sysobjects Where Name Like 'MyGroupedProc%' --It only list the Main Proc not other 2

ExecMyGroupedProc;2 1 --You can execute the hidden stored proc directly

nested cursors? @@FETCH_STATUS

Is there only one instance of @.@.FETCH_STATUS in T-SQL procs or triggers?
If I have two nested loops and I am using @.@.FETCH_STATUS to see when I am at
the end of the rowset....will the internal loop screw things up for the
external loop? Do I need to save @.@.FETCH_STATUS off to another variable and
use that to control the loop?
Will this work or fail:
DECLARE vcursor cursor local for
select i.id from inserted
Open vcursor
FETCH NEXT FROM vcursor into @.id
WHILE @.@.FETCH_STATUS=0
BEGIN
declare acursor cursor local for
select classcode FROM CLASSES WHERE ID=@.ID
Open acursor
FETCH NEXT FROM acursor into @.aclasscode
WHILE @.@.FETCH_STATUS=0
BEGIN
-- do processing
--
FETCH NEXT FROM acursor into @.aclasscode
END
CLOSE acursor
DEALLOCATE acursor
END
FETCH NEXT FROM vcursor into @.id
END
CLOSE vcursor
DEALLOCATE vcursor
eg will the internal loop reaching the last recods and setting
@.@.FETCH_STATUS=-1 cause the external loop to finish as well or do they each
have their own 'instance' of @.@.FETCH_STATUS
Al Blake, Canberra, AustraliaYour code will work fine. You are fetching the records in each of the loop
and @.@.FETCH_STATUS contains the latest value. So it won't make any problem
Babu M K
Comat Techonologies Pvt. Ltd.
"Al Blake" <al@._delete_this_.blakes.net> wrote in message
news:%231YQkILFFHA.3200@.TK2MSFTNGP10.phx.gbl...
> Is there only one instance of @.@.FETCH_STATUS in T-SQL procs or triggers?
> If I have two nested loops and I am using @.@.FETCH_STATUS to see when I am
at
> the end of the rowset....will the internal loop screw things up for the
> external loop? Do I need to save @.@.FETCH_STATUS off to another variable
and
> use that to control the loop?
> Will this work or fail:
> DECLARE vcursor cursor local for
> select i.id from inserted
> Open vcursor
> FETCH NEXT FROM vcursor into @.id
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> declare acursor cursor local for
> select classcode FROM CLASSES WHERE ID=@.ID
> Open acursor
> FETCH NEXT FROM acursor into @.aclasscode
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> -- do processing
> --
> FETCH NEXT FROM acursor into @.aclasscode
> END
> CLOSE acursor
> DEALLOCATE acursor
> END
> FETCH NEXT FROM vcursor into @.id
> END
> CLOSE vcursor
> DEALLOCATE vcursor
> eg will the internal loop reaching the last recods and setting
> @.@.FETCH_STATUS=-1 cause the external loop to finish as well or do they
each
> have their own 'instance' of @.@.FETCH_STATUS
> Al Blake, Canberra, Australia
>|||On Thu, 17 Feb 2005 16:32:56 +1100, Al Blake wrote:

>Is there only one instance of @.@.FETCH_STATUS in T-SQL procs or triggers?
>If I have two nested loops and I am using @.@.FETCH_STATUS to see when I am a
t
>the end of the rowset....will the internal loop screw things up for the
>external loop? Do I need to save @.@.FETCH_STATUS off to another variable and
>use that to control the loop?
>Will this work or fail:
Hi Al,
As Babu said: this will work. But it will probably be S-L-O-W.
From your code, I see absolutely no reason to use two nested cursors. You
can just do it in one cursor. Depending on what "-- do processing" really
is, you might even be able to do it without cursors at all.
Here's a version with just one cursor. If you need help to create a
completely set-based version, post some more information about what this
code actually does - and check out www.aspfaq.com/5006 for how to provide
the information.
DECLARE vcursor cursor local for
SELECT i.id, c.classcode
FROM inserted AS i
INNER JOIN classes AS c
ON c.ID = i.ID
Open vcursor
FETCH NEXT FROM vcursor into @.id, @.aclasscode
WHILE @.@.FETCH_STATUS=0
BEGIN
-- do processing
--
FETCH NEXT FROM vcursor into @.id
END
CLOSE vcursor
DEALLOCATE vcursor
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)