Recursive SP with Cursor... Getting Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Recursive SP with Cursor... Getting Error

Author

Topic

rdugre
Starting Member

32 Posts

Posted - 2003-11-07 : 19:05:40

I'm relatively new to writing stored procedures, but have a lot of experience writing dynamic SQL in ASP pages. I've got the following recursive stored procedure. It has been simplified for brevity.

The procedure accepts a group identifier as a parameter and iterates through a table of groups deleting the specified group and its children groups (and grandchildren groups). The procedure also does some other work to related tables, but I stripped that stuff out to simplify the code.

As you can see I am using a cursor (I know that's a bad thing). I am also getting an error because on the recurse it says the cursor is already in use. So, I need some help.

Can someone either:
1. Show me how to do this without using a cursor. OR
2. Show me how to give the cursor a different name each time the procedure is recursed into.


TABLE DEF
lngGroupID int (primary key)
lngParentGroupID int (index)
txtName varchar
...more


ALTER PROCEDURE dbo.sp_DeleteGroup
(
	@GroupID int
)
AS
	SET NOCOUNT ON
	
	DECLARE @SubID int
	
	DECLARE @SubGroupCursor CURSOR READ_ONLY
	FOR
	SELECT lngGroupID FROM tblGroups 
	WHERE (lngParentGroupID = @GroupID)
	
	OPEN SubGroupCursor
	FETCH SubGroupCursor INTO @SubID
	WHILE (@@FETCH_STATUS = 0) BEGIN
		EXEC tqm_sp_DeleteGroup @SubID
		FETCH SubGroupCursor INTO @SubID
	END
	CLOSE SubGroupCursor
	DEALLOCATE SubGroupCursor
		
	DELETE FROM tblTQMGroups
	WHERE (lngGroupID = @GroupID)
	
	RETURN

Thanks in advance to anyone that can help me out.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-07 : 19:13:43

You would need to post tqm_sp_DeleteGroup for us to help remove the cursor. I don't think anyone here is going to help you fix your cursor since cursors are bad. Well maybe Stoad will

BTW, you'll get faster answers if you provide DDL for your table rather than listing the columns. Also sample data with expected result set helps. You'll find the answers come pretty quick when we have all of the information. Sample data needs to be provided in the form of INSERT INTO statements.

Tara

rdugre
Starting Member

32 Posts

Posted - 2003-11-07 : 19:55:08

Thanks for the tips. Here is the additional information. Note: "tqm_sp_DeleteGroup" in the previous post should be "sp_DeleteGroup"... it is fixed below.

Here is the DDL for the table.


CREATE TABLE dbo.tblGroups (
	lngGroupID int NOT NULL,
	lngParentGroupID int NOT NULL,
	txtName varchar(255) NOT NULL
)

Here is some data for the table.


INSERT INTO tblGroups VALUES (1, 0, 'Group 1')
INSERT INTO tblGroups VALUES (2, 1, 'Group 2')
INSERT INTO tblGroups VALUES (3, 1, 'Group 3')
INSERT INTO tblGroups VALUES (4, 3, 'Group 4')
INSERT INTO tblGroups VALUES (5, 3, 'Group 5')

Here is the store procedure.


CREATE PROCEDURE dbo.sp_DeleteGroup
(
	@GroupID int
)
AS
	SET NOCOUNT ON
	
	DECLARE @SubID int
	
	DECLARE @SubGroupCursor CURSOR READ_ONLY
	FOR
	SELECT lngGroupID FROM tblGroups 
	WHERE (lngParentGroupID = @GroupID)
	
	OPEN SubGroupCursor
	FETCH SubGroupCursor INTO @SubID
	WHILE (@@FETCH_STATUS = 0) BEGIN
		EXEC sp_DeleteGroup @SubID
		FETCH SubGroupCursor INTO @SubID
	END
	CLOSE SubGroupCursor
	DEALLOCATE SubGroupCursor
		
	DELETE FROM tblGroups
	WHERE (lngGroupID = @GroupID)
	
	RETURN

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-07 : 20:36:38

LOL, Tara :)
========================================
Not quite sure... try this as a possible scheme:

ALTER PROCEDURE dbo.tqm_sp_DeleteGroup (@GroupID int)
AS
SET NOCOUNT ON
DECLARE @SubID int set @SubID=-1 -- or =''

while 0=0
begin
SELECT @SubID=min(lngGroupID) FROM tblGroups
WHERE (lngParentGroupID = @GroupID) and lngGroupID>@SubID
if @SubID is null break
----- here some code for each @SubID
EXEC tqm_sp_DeleteGroup @SubID
end

RETURN

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-07 : 22:40:14

The following method will work for adjacency set models when the maximum depth is known.
In this case this query will resolve a tree depth of 5 levels deep
This pattern can be extended in SQL Server to n number of levels by following the join pattern.

CREATE TABLE #tblGroups
(
lngGroupID int NOT NULL,
lngParentGroupID int NOT NULL,
txtName varchar(255) NOT NULL
)
go
INSERT INTO #tblGroups VALUES (1, 0, 'Group 1')
INSERT INTO #tblGroups VALUES (2, 0, 'Group 2')
INSERT INTO #tblGroups VALUES (3, 0, 'Group 3')
INSERT INTO #tblGroups VALUES (4, 1, 'Group 4')
INSERT INTO #tblGroups VALUES (5, 1, 'Group 5')
INSERT INTO #tblGroups VALUES (6, 1, 'Group 6')
INSERT INTO #tblGroups VALUES (7, 2, 'Group 7')
INSERT INTO #tblGroups VALUES (8, 2, 'Group 8')
INSERT INTO #tblGroups VALUES (9, 3, 'Group 9')
INSERT INTO #tblGroups VALUES (10, 3, 'Group 10')
INSERT INTO #tblGroups VALUES (11, 5, 'Group 11')
INSERT INTO #tblGroups VALUES (12, 5, 'Group 12')
INSERT INTO #tblGroups VALUES (13, 11, 'Group 13')
INSERT INTO #tblGroups VALUES (14, 11, 'Group 14')
INSERT INTO #tblGroups VALUES (15, 13, 'Group 15')
INSERT INTO #tblGroups VALUES (16, 13, 'Group 16')
go

select * from #tblgroups

declare @groupid int
set @groupid = 1

delete from #tblgroups where lnggroupid in
(
select
distinct d.lnggroupid
from
#tblgroups a
full outer join #tblgroups b on a.lnggroupid = b.lngparentgroupid
full outer join #tblgroups c on b.lnggroupid = c.lngparentgroupid
full outer join #tblgroups d on c.lnggroupid = d.lngparentgroupid
full outer join #tblgroups e on d.lnggroupid = e.lngparentgroupid
where
a.lnggroupid = @groupid or a.lngparentgroupid = @groupid
or b.lnggroupid = @groupid or b.lngparentgroupid = @groupid
or c.lnggroupid = @groupid or c.lngparentgroupid = @groupid
or d.lnggroupid = @groupid or d.lngparentgroupid = @groupid
)
go

select * from #tblgroups
go
drop table #tblgroups

EDIT: See Stoads solution below...

There are many good articles on this site related to Trees
http://www.sqlteam.com/item.asp?ItemID=8866

If the tree depth is unknown may want to look into a nested set model.
http://www.intelligententerprise.com/001020/celko1_1.shtml
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13233

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-08 : 08:17:21

Why should we limit ourselves with max level depth?

CREATE TABLE #tblGroups
(
lngGroupID int NOT NULL,
lngParentGroupID int NOT NULL,
txtName varchar(255) NOT NULL
)
go
INSERT INTO #tblGroups VALUES (1, 0, 'Group 1')
INSERT INTO #tblGroups VALUES (2, 0, 'Group 2')
INSERT INTO #tblGroups VALUES (3, 0, 'Group 3')
INSERT INTO #tblGroups VALUES (4, 1, 'Group 4')
INSERT INTO #tblGroups VALUES (5, 1, 'Group 5')
INSERT INTO #tblGroups VALUES (6, 1, 'Group 6')
INSERT INTO #tblGroups VALUES (7, 2, 'Group 7')
INSERT INTO #tblGroups VALUES (8, 2, 'Group 8')
INSERT INTO #tblGroups VALUES (9, 3, 'Group 9')
INSERT INTO #tblGroups VALUES (10, 3, 'Group 10')
INSERT INTO #tblGroups VALUES (11, 5, 'Group 11')
INSERT INTO #tblGroups VALUES (12, 5, 'Group 12')
INSERT INTO #tblGroups VALUES (13, 11, 'Group 13')
INSERT INTO #tblGroups VALUES (14, 11, 'Group 14')
INSERT INTO #tblGroups VALUES (15, 13, 'Group 15')
INSERT INTO #tblGroups VALUES (16, 13, 'Group 16')
go

create table #t (lev int, child int)
declare @lev int set @lev=0
insert into #t select @lev, 11 -- << zero-level parent

while @@rowcount>0
begin
set @lev=@lev+1
insert into #t select @lev, lngGroupID from #tblGroups
where lngParentGroupID in (select child from #t where lev=@lev-1)
end

delete from #tblGroups where lngGroupID in
(select child from #t where lev>0)

select * from #tblGroups
drop table #tblGroups
drop table #t

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-08 : 08:43:47

Nice solution Stoad!! and #t gives him/her a reference for further processing..

rdugre
Starting Member

32 Posts

Posted - 2003-11-08 : 09:56:08

Thanks a bunch, Stoad! I'll give that a shot... it definitely looks like what I want to do.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-08 : 10:46:52

LOL... guys :)

as I can remember I learned this trick from nr...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-08 : 11:52:36

Not sure if this is basically the same as what Stoad posted, but I've posted a similiar technique as well:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25964

That gives a UDF to return all children from any node in a tree of any depth. Works pretty well, too !

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-09 : 04:01:36

Jeff,

it's exactly the same. Hardly can we expect to see
something essentially new and better. Maybe only in
Yukon with its recursive queries.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources