June 12, 2006 at 2:26 pm
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
June 13, 2006 at 6:15 am
From Books On line: The syntax for declaring a cursor is:
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.
June 13, 2006 at 6:28 am
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...
June 13, 2006 at 8:18 am
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
June 13, 2006 at 1:06 pm
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
June 13, 2006 at 1:14 pm
Typo:
EXECUTE showAdminsXML @gpMember
s/be execute showMembersXML @gpMember
June 17, 2006 at 11:55 pm
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
- Ward Pond
blogs.technet.com/wardpond
June 19, 2006 at 6:42 am
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