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

The error makes sense as you are doing recursive calls to CopyObject, which would try to create a new cursor called "c" before closing and destroying the last one.

You could try CLR stored procedure (assuming you have SQL Server 2005), from where (e.g. in C#) you can also do recursion, but without the cursor name confict problem.

|||

I made the whole script dynamic instead, adding an extra parameter @.cycledepth, that i increment every time the proc calls itself, and then call the cursor 'c' + cast(@.cycledepth as varchar). That did the trick, although it's a bit slower tahn i had hoped, but that's probably because of a few triggers i should disable and then enable at the end.