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 |
zez0
Starting Member
24 Posts |
Posted - 2010-12-28 : 08:43:34
|
Hi All , Can any one tell me where the proplem this is the PROCEDURE and this is the error when it execute:A cursor with the name 'Section' already exists.The cursor is already open.A cursor with the name 'Section' already exists.The cursor is already open.A cursor with the name 'Section' does not exist.A cursor with the name 'Section' does not exist.A cursor with the name 'Section' does not exist.A cursor with the name 'Section' does not exist.A cursor with the name 'Section' does not exist.A cursor with the name 'Section' does not exist. quote: set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[TypeInSections_DeleteAll] (@SectionId int)ASdeclare @ParentCoutn int,@DumySectionIs intBEGINset @ParentCoutn= (select count(sectionParentId) from sections where sectionParentId=@SectionId)if(@ParentCoutn>0) begin declare Section cursor for select SectionId from sections where sectionParentId=@SectionId open Section fetch Section into @DumySectionIs WHILE @@Fetch_Status = 0 begin execute TypeInSections_DeleteAll @DumySectionIs fetch Section into @DumySectionIs end CLOSE Section DEALLOCATE Section end else begin delete from TypesInsection where sectionId=@SectionId endEND
zezo |
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-28 : 09:19:31
|
You have a recursive stored procedure (for no real reason it seems) but a cursor is in scope for the called sp so you get an error.You could try a local cursor but I think it would be better to get rid of the cursors and recursive SP and just code a loop.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|