Calling cursor into cursor(Nested cursor)

Question

I am facing problem in calling cursor into cursor, it's throwing "A cursor with the name 'ParentDetail' already exists."

Here is my SP, Please have a look :

Alter PROCEDURE FetchChild
    @ChildID Int
AS
BEGIN
    SET NOCOUNT ON;
    Create Table #AllChilds
    (
        ID  Int,
        Name Varchar(100)
    )
    Declare @ID Int
    Declare @Name Varchar(100)

    Declare ParentDetail Cursor For 
            Select ID,Name From Contacts Where ISNULL(ParentID,0) = @ChildID
        Open ParnetDetail
            Fetch Next From ParentDetail Into @ID,@Name
            While @@FETCH_STATUS = 0
            Begin
                Insert Into #AllChilds
                Values(@ID,@Name)

                Insert Into #AllChilds
                EXEC FetchChild
                    @ChildID = @ID

                Fetch Next From ParentDetail Into @ID,@Name
            End
            Close ParentDetail
            Deallocate ParentDetail
            Select * From #AllChilds
END
GO

it self calling store procedure. It is problem. It may be remove — Mukesh Kalgude, Jul 20 '15 at 07:21
Looks like a job for a [Recursive CTE](https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx) — Damien_The_Unbeliever, Jul 20 '15 at 07:22
I presume you are aware that you recursively call the `FetchChild` procedure? If that's your intention though then this will fail, because in subsequent recursive executions of the procedure, `ParentDetail` has already been defined (and cannot be again in this context). — Martin, Jul 20 '15 at 07:22
This would also have failed if it tried to recurse twice since [Insert Exec cannot be nested](http://stackoverflow.com/q/3795263/15498) — Damien_The_Unbeliever, Jul 20 '15 at 07:25
Thanx for the reply Yes i know that, but i have some other data modifications with the data retrieval so i cant use cte for this, is there any way to resolve this with cursor. — user3141674, Jul 20 '15 at 07:33
Just as @Damien_The_Unbeliever the said, this looks like a job for a recursive CTE to generate the parent-child combinations, which then could be put into a temporary table and next moved into the `INSERT` statement with a `INSERT INTO .. SELECT` — Radu Gheorghiu, Jul 20 '15 at 07:34
Perhaps, instead of showing us the failed solution, you could present us with the *problem* to be solved. See [Tips for asking a good Structured Query Language (SQL) question](http://meta.stackoverflow.com/q/271055/15498) — Damien_The_Unbeliever, Jul 20 '15 at 07:36

score 0 · Answer 1 · answered Jul 20 '15 at 08:50

0

Try this, change

Declare ParentDetail Cursor For

to

Declare ParentDetail Cursor Local For

answered Jul 20 '15 at 08:50

Pawel Czapski

1,856
2
16
26

It works for me, looks like previous time cursor wasn't closed or deallocated, above should solved this problem – Pawel Czapski Jul 20 '15 at 08:53

Calling cursor into cursor(Nested cursor)

1 Answers1