Calling a Cursor with same name

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 2005 Forums

Transact-SQL (2005)

Calling a Cursor with same name

Author

Topic

burnseymufc
Starting Member

4 Posts

Posted - 2008-10-09 : 06:21:56

I am having a major problem with a cursor! I have been trying to solve this for over 2 weeks now an can't. I will start off with explaining the problem. I have a table called Themes and inside here the user can add a module. A module is at the top level and has a parent of 0. The user can add a sub-module to a module and this sub-module will have a parent of the module id. A further sub-module can be added to a sub-module and this will have a parent of the sub-module id. This can go to infinity. I know the table is badly designed, but, it was already like this when I arrived at the company and it would take longer to rectify this. Below is my Stored Procedure of how to get around this. I want the stored procedure call itself with new parameter id, but everytime I do this, an error displays stating that the Cursor already exists and then it doesn't exist in another error message. Is there anyway I could do this

ALTER PROCEDURE procModulesAndUnderDelete @parentId int

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @IDs int
DECLARE @newParentId int

IF EXISTS(SELECT id FROM themes WHERE parent = @parentId)
BEGIN
DECLARE cursorIDs CURSOR FOR
(SELECT id FROM themes WHERE parent = @parentId)

OPEN cursorIDs
FETCH NEXT FROM cursorIDs INTO @IDs
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS(SELECT id FROM themes WHERE id = @IDs)
BEGIN
SET @newParentId = (SELECT id FROM themes WHERE id = @IDs)
EXEC procModulesAndUnderDelete @newParentId
FETCH NEXT FROM cursorIDs INTO @IDs
END
END
CLOSE cursorIDs
DEALLOCATE cursorIDs
END

DELETE FROM themes
WHERE id = @parentId
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 06:25:44

now that you've posted this in sql 2005, i assume you're working in 2005. then it would be very easy for you to do this using recursive ctes available in sql 2005. search for recursive ctes in books online.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources