Copy Subtree, Recursive Sproc With Cursor Doesn't Work

Hi all,

I have a parent-child table, and i want to copy subtrees of it, so for instance this would be the starting point:
(id, parentId, label)
0, null, World
1, 0, US
2, 1, NY
3, 0, UK
4, 3, London

now i want to copy object 3 (UK) and it's children, so i would get
0, null, World
1, 0, US
2, 1, NY
3, 0, UK
4, 3, London
5, 0, UK_copy
6, 5, London_copy


I have this sproc:



Code Snippet

alter proc CopyObject


(@ObjectId int,

@NewParentId int)

as


declare @NewId int,

@NewName varchar


select @NewId = max(Id) + 1 from Object

select @NewName = [Name] + 'copy' from [Object] where Id = @ObjectId


-- copy object

INSERT INTO [Object]


([Id]

,[Name]

,[ParentId]

select @NewId,


@NewName,

@NewParentId

from [Object]

where Id = @ObjectId


-- copy children and set their parent to the newly created object

declare c cursor fast_forward for


select Id

from [Object]

where ParentId = @ObjectId


declare @ChildId int


open c

fetch next from c into @ChildId


while @@fetch_status = 0

begin


exec CopyObject


@ObjectID = @ChildId,

@NewParentId = @NewId

fetch next from c into @ChildId

end

close c

deallocate c





But htis throws an error that the cursor already exists:

Msg 16915, Level 16, State 1, Procedure CopyObject, Line 66

A cursor with the name 'c' already exists.

Msg 16905, Level 16, State 1, Procedure CopyObject, Line 72

The cursor is already open.

I've tried to think of an approach without cursors, but i can't figure it out. Because on the first pass, the new parentId will be the same as the parentId of the object to be copied. But the copies of the children of this first original object should have the parentid set to id of the copied object, and so all the way down the tree.

Any ideas?

Thanks in advance,

Gert-Jan