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.
| Author |
Topic |
|
burnseymufc
Starting Member
4 Posts |
 Posted - 2008-10-09 : 06:22:24
|
| 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 thisALTER PROCEDURE procModulesAndUnderDelete @parentId intASBEGIN -- 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 = @parentIdEND |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
Calling a Cursor with same name