Recursive Stored Procedures and Cursors

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

SQL Server Development (2000)

Recursive Stored Procedures and Cursors

Author

Topic

JHotchkiss
Starting Member

6 Posts

Posted - 2003-11-10 : 06:24:05

Hi I have a tree table which I want to delete records from (I would prefer not to use a recursive trigger as I have had problems using these with replication). My procedure is as follows:

CREATE PROCEDURE spDeleteActorSoftware
@iActorID INT,
@iFileID INT
AS
DECLARE @iSuperActorID INT
DECLARE software_cursor CURSOR FAST_FORWARD FOR
SELECT ActorID
FROM Actor
WHERE SuperActorID = @iActorID

----------------------------------------------------------------------------------------

DELETE FROM ActorSoftware
WHERE ActorID = @iActorID
AND FileID = @iFileID

----------------------------------------------------------------------------------------

OPEN software_cursor

FETCH NEXT FROM software_cursor
INTO @iSuperActorID

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC spDeleteActorSoftware @iSuperActorID, @FileID
FETCH NEXT FROM software_cursor
INTO @iSuperActorID
END

----------------------------------------------------------------------------------------

CLOSE software_cursor
DEALLOCATE software_cursor
GO

on running it I get the following errors:

(0 row(s) affected)

Server: Msg 16915, Level 16, State 1, Procedure spDeleteActorSoftware, Line 7
A cursor with the name 'software_cursor' already exists.

(0 row(s) affected)

Server: Msg 16905, Level 16, State 1, Procedure spDeleteActorSoftware, Line 20
The cursor is already open.
Server: Msg 16916, Level 16, State 1, Procedure spDeleteActorSoftware, Line 28
A cursor with the name 'software_cursor' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure spDeleteActorSoftware, Line 32
A cursor with the name 'software_cursor' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure spDeleteActorSoftware, Line 33
A cursor with the name 'software_cursor' does not exist.

I any ideas on what the workaround is?

Cheers

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-10 : 07:11:41

See this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30422

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-11-10 : 07:15:42

Try this:

http://www.sqlteam.com/item.asp?ItemID=8866

If you want to delete all the children of a node, try:

DELETE E FROM Employee E
INNER JOIN Tree T ON E.EmployeeID=T.EmployeeID
INNER JOIN Tree B ON T.ParentNode=B.Node
WHERE T.Lineage LIKE '/' + Cast(T.ParentNode as varchar) + '/'
AND B.EmployeeID=1003 --All Ian Faith's "children"

get deleted

JHotchkiss
Starting Member

6 Posts

Posted - 2003-11-25 : 05:27:23

Thanks for the responses, I got some inspiration from them, I basically ended up with this, FYI (replaced leading tabs/spaces with _ to keep formatting, just search and replace in text editor with space):

CREATE PROCEDURE spDeleteActorSoftware
____@iActorID INT,
____@iFileID INT
AS
____-- Create a temporary table to store the ID's of the records to delete, also the hierarchy level, used to get the next level
____-- NOTE: primary key of table selecting from is ActorID, FileID as it is an intersection table
____CREATE TABLE #tmpActorSoftware (ActorID INT NOT NULL, Level INT NOT NULL)
____CREATE UNIQUE INDEX idxActorSoftware ON #tmpActorSoftware(ActorID, Level)

____-- Stores the current hierarchy level (set to first level
____DECLARE @iLevel INT
____SET @iLevel = 1

____-- Insert the ID's of all first level records into the temp table - level set to 1
____INSERT INTO #tmpActorSoftware (ActorID, Level)
________SELECT ActorID, @iLevel
________FROM ActorSoftware
________WHERE ActorID = @iActorID
________AND FileID = @iFileID

____-- Repeat while rows are returned, when not, will have reached bottom level
____WHILE (@@ROWCOUNT > 0)
____BEGIN
________-- Increase level number
________SET @iLevel = @iLevel + 1

________-- Insert into temp table the next level rows, where the parent/super id of equals the row id
________-- of the previous level (level minus 1), lookup the row id of the child by accessing the Actor table
________-- as hierarchy is not held in the insection table being deleted from
________INSERT INTO #tmpActorSoftware (ActorID, Level)
____________SELECT A.ActorID, @iLevel
____________FROM #tmpActorSoftware T, Actor A, ActorSoftware S
____________WHERE T.Level = (@iLevel - 1)
____________AND T.ActorID = A.SuperActorID
____________AND A.ActorID = S.ActorID
____________AND S.FileID = @iFileID
____END

____-- Delete all rows where rowId the same as rows in temporary table and file id matches specified id
____DELETE ActorSoftware
____FROM ActorSoftware A, #tmpActorSoftware B
____WHERE A.ActorID = B.ActorID
____AND A.FileID = @iFileID
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources