A cursor within another sqlserver cursor

2

I know I could do a join of tab1 with tab2, but that would not be the case, I need the two cursors.

When I run I get the error:

A cursor with the name 'cursor_tab2 ' already exists.

The cursor is already open.

I even understand the error, but I do not know how to solve it.

What is the right way to do this?

I have used it.

DECLARE cursor_tab1 CURSOR
    FOR SELECT id FROM tab1
    OPEN cursor_tab1
    FETCH NEXT FROM cursor_tab1
    INTO @idTab1

    WHILE @@FETCH_STATUS = 0
    BEGIN


        DECLARE cursor_tab2 CURSOR
        FOR SELECT id FROM tab2 where id_tab1 = @idTab1
        OPEN cursor_tab2
        FETCH NEXT FROM cursor_tab2
        INTO @idTab2

        WHILE @@FETCH_STATUS = 0
        BEGIN

            ...


        FETCH NEXT FROM cursor_tab2 INTO @idTab2;
        END



    FETCH NEXT FROM cursor_tab1 INTO @idTab1;
    END
    
asked by anonymous 07.05.2015 / 19:48

1 answer

3

First of all it should be said that there are few problems that require cursors to be solved in SQL.

That being said, you're apparently not closing and releasing (deallocate) the cursors.

CLOSE cursor_tab2
DEALLOCATE cursor_tab2

If you are and that is not the problem, then declare the cursor as LOCAL.

DECLARE teuCursor CURSOR LOCAL ...
    
07.05.2015 / 20:00