recursive stored procedure: has a cursor

  • I have a stored procedure that is recrusive: the sp has a cursor.

    when I try to execute this I get the following error:

     

    Server: Msg 16915, Level 16, State 1, Proceduresp_1, Line 43

    A cursor with the name 'gp_grp' already exists.

     

    how do I get over this,

    thanks

  • From Books On line:  The syntax for declaring a cursor is: 

    Transact-SQL Extended Syntax

    DECLARE cursor_name CURSOR

    [ LOCAL | GLOBAL ]

    [ FORWARD_ONLY | SCROLL ]

    [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

    [ TYPE_WARNING ]

    FOR select_statement

    [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

    LOCAL

    Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is only valid within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.

    GLOBAL

    Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect.

    Note  If neither GLOBAL or LOCAL is specified, the default is controlled by the setting of the default to local cursor database option. In SQL Server version 7.0, this option defaults to FALSE to match earlier versions of SQL Server, in which all cursors were global. The default of this option may change in future versions of SQL Server.

    So if your stored procedure must recursively call itself, then declare your cursor as LOCAL.  I would seriously look at my code to determine if recursion is absolutly necessary. Recursion is a bit of a performance drag.

  • Mine is sqlserver 2000: so the cursor is local be default.

    In my case : this being a qtrly report, and data is hierarchal: I think I have no other options...

     

  • Is there the possibility of you posting the code? There may be a way to eliminate the cursor. That would allow your stored procedure to execute recursively as a result.

    K. Brian Kelley
    @kbriankelley

  • The local works fine: tests also came up as expected. I am not sure if this is the right way:

    I am trying to output an XML that I will import to Excel: maybe this is not the correct way.

    I have a group with 48 members: 45 are nested groups, and my xml output is way out of order.

    The max nesting I see is 5 levels. Maybe I need to revisit the solution.

    The final output  should be the Group name with members:

    The table groupMembers has the groups and members. The table groups is distinct groups from groupMembers which I used to filter: direct members and nested members.

     

    CREATE PROCEDURE showMembersXML(@currGP char(200))

    AS

    --''''''''''''''''''''''''''''''''''' showMembersXML'''''''''''''''''''''''''''''''''''''''''''''''''''''''

    --          Input Parameter: group name

    --  Recrusively expands all the nested groups.

    --             2 step process: 1. output the users

    --                           2. expand the groups: recrusive calls

    --'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

     

    DECLARE @gpName  char(200), @gpMember char(500), @prt tinyint

     

     -- 1. users ....

    DECLARE gp_mem CURSOR FOR

     SELECT * from GroupMembers

      where gpName=@currGP

       and gpMember not in (select gpName from SPgroups)

       

    OPEN gp_mem

    FETCH NEXT FROM gp_mem INTO  @gpName, @gpMember

    WHILE (@@FETCH_STATUS=0)

    BEGIN

     PRINT '<spmember>'+ltrim(rtrim(@gpMember)) + '</spmember>'

     

     FETCH NEXT FROM gp_mem INTO @gpName, @gpMember

     

    END

    CLOSE gp_mem

    DEALLOCATE gp_mem

     

    --2. Groups ...

    DECLARE gp_grp CURSOR LOCAL FOR

     SELECT * FROM GroupMembers

      WHERE gpName=@currGP

       AND gpMember IN (SELECT gpName FROM SPgroups)

        Order By gpMember

       

    OPEN gp_grp

    FETCH NEXT FROM gp_grp INTO @gpName, @gpMember

    WHILE (@@FETCH_STATUS=0)

    BEGIN

     PRINT '<spgroup>' +  ltrim(rtrim(@gpMember))

            SELECT @prt=1

     -- Call itself

     EXECUTE showAdminsXML @gpMember

                   

     FETCH NEXT FROM gp_grp INTO @gpName, @gpMember

     

    END

    IF @prt=1

     PRINT '</spgroup>'

     SELECT @prt=0

     

    CLOSE gp_grp

    DEALLOCATE gp_grp

     

    GO

     

  • Typo:

    EXECUTE showAdminsXML @gpMember

     

    s/be execute showMembersXML @gpMember

     

  • Hello..

    If you build a temporary structure (table variable or temp table) that contains your hierarchy in the properly sorted order, you should then be able to use that to drive the generation of your XML.  I would think that the cursor would disappear from the scenario entirely, but you may want to use it to populate the temporary structure.  That's up to you..

    Hope this helps..

        - Ward Pond


    Take care..

    - Ward Pond
    blogs.technet.com/wardpond

  • Here is what I did:

    Used the same SP with a temp table having an identity field.

    Populate data from both the cursors.

    Read temp table order by the identity field.

     

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply