Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

Recursion in Stored Procedure

Hi Experts,

I am trying to call a store procedure in recursion. The problem I face is that there is a cursor in that stored procedure and when the stored procedure is executed, an error says that a cursor with similar name is already declare and open.

I have pasted the the stored procedure below, please help me how to overcome the problem. Can we:
1) use a dynamic cursor name so that it changes in each level of calling,
OR
2) define a scope for the cursor, so that the same cursor name can be reused but wont afftect other levels.

Please advice and help as soon as possible.

Thanks,
Suman Ghosh
---------------------------------------------------------------------------------------------

/*
      Name:             usp_ManageStatusOfChildCategory
      Purpose:      Activate/Inactivate Child Category Under a Category
      Return type:      Integer
      Author:            Suman Ghosh
      Date:            02.06.2006
*/
ALTER PROCEDURE [dbo].[usp_ManageStatusOfChildCategory]
      @Parent_Category_Id      Int,
      @ActiveStatus      Bit,
      @retVal      Int      Output
AS
Declare @SubCategoryCount Int
Select @SubCategoryCount = Count(Category_Id) From tblCategory
Where
      Parent_Category_Id = @Parent_Category_Id

If (@SubCategoryCount = 0)
Begin
      Print 'Level 1 # Update Category_Id: ' + Cast(@Parent_Category_Id As Varchar(20))
      Begin Transaction
      Update tblCategory
      Set
            ActiveStatus = @ActiveStatus
      Where
            Category_Id = @Parent_Category_Id
      If (@@Error = 0)
      Begin
            Commit Transaction
            Set @retVal = 1
      End
      Else
      Begin
            Rollback Transaction
            Set @retVal = 0
      End
End
Else
Begin
      Print 'Level 1 # Cursor For Category_Id: ' + Cast(@Parent_Category_Id As Varchar(20))

      DECLARE @Category_Id Int
      DECLARE Category_Cursor CURSOR FOR
      SELECT Category_Id FROM tblCategory
      WHERE Parent_Category_Id = @Parent_Category_Id
      ORDER BY Category_Id
      
      OPEN Category_Cursor
      
      FETCH NEXT FROM Category_Cursor
      INTO @Category_Id
      
      WHILE @@FETCH_STATUS = 0
      BEGIN
            Print 'Level 2 # Cursor For Category_Id: ' + Cast(@Parent_Category_Id As Varchar(20)) + ' | Category_Id: ' + Cast(@Category_Id As Varchar(20))
            DECLARE @Count Int
            exec usp_ContainsSubCategories @Category_Id, @Count Output
            if @Count = 1
            Begin
                  Print 'Level 3 # Recursion Category_Id: ' + Cast(@Category_Id As Varchar(20))
                  DECLARE @Result Int
                  exec usp_ManageStatusOfChildCategory @Category_Id, @ActiveStatus, @Result
                  Print 'Level 3 # Update Category_Id: ' + Cast(@Category_Id As Varchar(20))
                  Begin Transaction
                  Update tblCategory
                  Set
                        ActiveStatus = @ActiveStatus
                  Where
                        Category_Id = @Category_Id
                  If (@@Error = 0)
                  Begin
                        Commit Transaction
                        Set @retVal = 1
                  End
                  Else
                  Begin
                        Rollback Transaction
                        Set @retVal = 0
                  End
            End
            Else
            Begin
                  Print 'Level 2 # Update Category_Id: ' + Cast(@Category_Id As Varchar(20))
                  Begin Transaction
                  Update tblCategory
                  Set
                        ActiveStatus = @ActiveStatus
                  Where
                        Category_Id = @Category_Id
                  If (@@Error = 0)
                  Begin
                        Commit Transaction
                        Set @retVal = 1
                  End
                  Else
                  Begin
                        Rollback Transaction
                        Set @retVal = 0
                  End
            End
               -- Get the next Category
            FETCH NEXT FROM Category_Cursor
            INTO @Category_Id
      END
      
      CLOSE Category_Cursor
      DEALLOCATE Category_Cursor
End
0
SumanGhosh
Asked:
SumanGhosh
1 Solution
 
LowfatspreadCommented:
replace your cursor processing with a temp table (variable) and a while loop...

e.g.

Declare @Temptb Table as (categoryID int,identity(int,1,1) as rowid)
declare @max int
declare @rowid int

Insert into @temptb
     SELECT Category_Id
       FROM tblCategory
     WHERE Parent_Category_Id = @Parent_Category_Id
     ORDER BY Category_Id
Set @max=@@RowCount

set @rowid=1
while @rowid<@max
begin
    select @category_id =category_id , @rowid=@rowid+1
       from @temptb
      where rowid=@rowid
    .... do what you want...
end

       
0
 
SumanGhoshAuthor Commented:
Hey I just found the easiest solution. We just have to change the Cursor declaration as follows:

DECLARE Category_Cursor CURSOR LOCAL SCROLL FOR
      SELECT Category_Id FROM tblCategory
      WHERE Parent_Category_Id = @Parent_Category_Id
      ORDER BY Category_Id

I would request the moderator to reverse my point back to my account, since my solution is correct and works.
0
 
LowfatspreadCommented:
so the procedure becomes this...

/*
     Name:           usp_ManageStatusOfChildCategory
     Purpose:     Activate/Inactivate Child Category Under a Category
     Return type:     Integer
     Author:          Suman Ghosh
     Date:          02.06.2006
*/
ALTER PROCEDURE [dbo].[usp_ManageStatusOfChildCategory]
     @Parent_Category_Id     Int,
     @ActiveStatus     Bit,
     @retVal     Int     Output
AS
Declare @SubCategoryCount Int
Select @SubCategoryCount = Count(Category_Id) From tblCategory
Where
     Parent_Category_Id = @Parent_Category_Id

If (@SubCategoryCount = 0)
Begin
     Print 'Level 1 # Update Category_Id: ' + Cast(@Parent_Category_Id As Varchar(20))
     Begin Transaction
     Update tblCategory
     Set
          ActiveStatus = @ActiveStatus
     Where
          Category_Id = @Parent_Category_Id
     If (@@Error = 0)
     Begin
          Commit Transaction
          Set @retVal = 1
     End
     Else
     Begin
          Rollback Transaction
          Set @retVal = 0
     End
End
Else
Begin
     Print 'Level 1 # Cursor For Category_Id: ' + Cast(@Parent_Category_Id As Varchar(20))

     DECLARE @Category_Id Int
     Declare @Temptb Table as (categoryID int,identity(int,1,1) as rowid)
     declare @max int
     declare @rowid int
     DECLARE @Count Int

     Insert into @temptb
     SELECT Category_Id
       FROM tblCategory
     WHERE Parent_Category_Id = @Parent_Category_Id
     ORDER BY Category_Id
     Set @max=@@RowCount

     set @rowid=1

     while @rowid<@max
     BEGIN
          select @category_id =category_id , @rowid=@rowid+1
            from @temptb
           where rowid=@rowid

          Print 'Level 2 # Cursor For Category_Id: ' + Cast(@Parent_Category_Id As Varchar(20)) + ' | Category_Id: ' + Cast(@Category_Id As Varchar(20))
         
          exec usp_ContainsSubCategories @Category_Id, @Count Output
          if @Count = 1
          Begin
               Print 'Level 3 # Recursion Category_Id: ' + Cast(@Category_Id As Varchar(20))
               DECLARE @Result Int
               exec usp_ManageStatusOfChildCategory @Category_Id, @ActiveStatus, @Result
               Print 'Level 3 # Update Category_Id: ' + Cast(@Category_Id As Varchar(20))
               Begin Transaction
               Update tblCategory
               Set
                    ActiveStatus = @ActiveStatus
               Where
                    Category_Id = @Category_Id
               If (@@Error = 0)
               Begin
                    Commit Transaction
                    Set @retVal = 1
               End
               Else
               Begin
                    Rollback Transaction
                    Set @retVal = 0
               End
          End
          Else
          Begin
               Print 'Level 2 # Update Category_Id: ' + Cast(@Category_Id As Varchar(20))
               Begin Transaction
               Update tblCategory
               Set
                    ActiveStatus = @ActiveStatus
               Where
                    Category_Id = @Category_Id
               If (@@Error = 0)
               Begin
                    Commit Transaction
                    Set @retVal = 1
               End
               Else
               Begin
                    Rollback Transaction
                    Set @retVal = 0
               End
          End
         
     END

End

Return


BTW i'd expect you to exit your loops if an error occurs for which you'd rollback a sub transaction....
are you sure that you want to rollback only the subtransactions?


hth
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LowfatspreadCommented:
fine but you'll get better performance by removing the cursor processing and using the temptable...
0
 
SumanGhoshAuthor Commented:
Hi Lowfatspread,

Thanx for your comment too, i have never used the method you have shown. I will definitely try that out. But if you can show me a way to roll back the entire transaction and not just sub transactions if an error occurs at any level, then I can donate this point to you. But for the time being, please keep the cursor way and still show me the way to entirely rollback all the  transactions in case of an error.

Thanx
Suman Ghosh.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with Lowfatspread that non-cursor solution will perform better.

in any case, to rollback, simply issue the line
ROLLBACK
and the entire transaction will rollback.

in case of error:
you have to check, after EACH line you do someting, on the @@ERROR value.

0
 
imran_fastCommented:
Replace your cursor
DECLARE Category_Cursor CURSOR FOR
     SELECT Category_Id FROM tblCategory
     WHERE Parent_Category_Id = @Parent_Category_Id
     ORDER BY Category_Id

with cursor variable


Replace your cursor with cursor variable

DECLARE @Category_Cursor CURSOR

set @Category_Cursor = cursor
     FOR
     SELECT Category_Id FROM tblCategory
     WHERE Parent_Category_Id = @Parent_Category_Id
     ORDER BY Category_Id

0
 
SumanGhoshAuthor Commented:
To: angelIII
I had already used Rollback in my code, but Lowfatspread said it would just affect as sub transaction rollback. Is there any way to completely rollback all tranactions?

To imran_fast:
How will changing the cursor declaration to cursor variable affect my requirement? Please explain.
0
 
LowfatspreadCommented:
your code

exec mainSP parm1,...,retval output

if retval = 1 then 'Rollback....'


Mainsp
  parms...

begin

Begin transaction

 exec yourCurrentSP ....
 if @retval = 1
 begin
     Rollback TRANSACTION
 end
else
   commit transaction
end

return
 
in YOURCURRENT sp

remove all BEGIN TRANSACTION/ ROLLBACK CODE
unless it is really part of the required processing... (exceptional requirement)
 ie you write you code on the basis that your doing whatif style processes that
    you expect to backout before making an overall choice/decision...



You seem to be using transactions at an inappropriate level...
In that you are nesting the transaction levels...
What is the UNIT OF WORK (UOW) for your business process...
that is what you should be protecting within an overall transaction...

since you know that your existing stored procedure will be used recursively then whats the point of transactionalising
each invoked level.. the transaction is presumably at least just at the initial invokation stage of the process
which is why i'm suggesting you should have a driver stored procedure for the overall process.... that will have the
begin / commit / rollback transaction processing within it... the recursive and subordinate procedures responsibility
should be to report errors back up to the originator which can then commit/rollback appropriately...

ultimately you client side (application logic) is the place for the commit/rollback decisions to be taken...
as they will know have this part of the UOW affects the overall User/application transaction...


hth  



0
 
SumanGhoshAuthor Commented:
So, can i just wrap the first level of this procedure call within a transaction and remove transaction from within this procedure, since I shall actually be calling this procedure from another procedure.
0
 
LowfatspreadCommented:
consider...

Begin Transaction (A)

   ....

    Begin Transaction (B)

   ....
IF ...
      commit trans  (forB)
else
      rollback trans (ForB)


    Begin Transaction (C)

   ...
IF ..
      commit trans  (forC)
else
      rollback trans (ForC)

  ...

 IF ...
  commit trans  (forA)
else
  rollback trans (ForA)


if you rollback / commit at FORC
then you only affect the operations performed between C and FORC
similarly  
if you rollback / commit at FORB
then you only affect the operations performed between C and FORB

you can have rolled back both either or none  of these sub trabsactions (B,C)
and the action you take at FORA will determine what is actually commited to the Database...

so if B works but C fails (and was rolled back)
and you commit A
then pure A operations will be saved together with whatever B did...
if you rollback (at FORA) then even though you commited B & C there actions will not be committed to the database....


hth
 
0
 
SumanGhoshAuthor Commented:
Thanx
0
 
LowfatspreadCommented:
Probably YES

As i was attempting to explain sub transactions are only appropriate if your process is attempting to "manage" a complex
operation that has options/choices available to it to enable recovery-processing...


e.g. if its a workflow process...
you may attempt to drive as many workflow tasks as possible forward with the one operation...
however if problems are encounter in the completion of (some of the) future/follow workflow activities
then you'll need to backout any activity for that process, and set up that activity to be retried independantly later
as part of the normal workflow activity select cycle... whilst still attempting to process any other outstanding
related activities...



   
0
 
SumanGhoshAuthor Commented:
I just removed all the transactions within the procedure and did this while calling the procedure:

Begin Transaction
Declare @retVal Int
exec usp_ManageStatusOfChildCategory 1, 0, @retVal Output
If @retVal = 1
      Commit Transaction
Else
      Rollback Transaction

And it works perfectly.

Thanks once again
0
 
LowfatspreadCommented:
just make sure that your while loops stop processing when an error is detected and "immediatly" return to their caller...

probably

while (orignal while conditions) and Retval = 0
begin
...
    if @@error... set @retval=1
end

if @retval=1  then return...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now