How to use cursor within cursor in SQL Server?

Question

I have the following query, I want use a nested cursor in my query. How to do this, because it's not running and I am new to SQL Server. Please help me

CHECK TABLE SUGGEST LAT 31.8181 LONG 71.4146

Msg 16915, Level 16, State 1, Procedure Sp_CheckCarStatusMeter, Line 266
A cursor with the name 'ShapeCursor' already exists. ELSE OPEN CURSOR

Msg 16905, Level 16, State 1, Procedure Sp_CheckCarStatusMeter, Line 296
The cursor is already open.

Code:

DECLARE SuggestCursor CURSOR FOR  
    SELECT TOP 100 
        rtha.car_id, rtha.latitude, rtha.longitude 
    FROM   
        Carhistory rtha 
    WHERE 
        rtha.car_id = 6142 ;

OPEN SuggestCursor;  

FETCH NEXT FROM SuggestCursor INTO @CarSuggested, @carlatprevious, @carlongprevious;  

WHILE (@@FETCH_STATUS = 0)  
BEGIN  
    PRINT 'CHECK TABLE SUGGEST LAT '+@carlatprevious +' LONG '+ @carlongprevious;

    DECLARE ShapeCursor CURSOR FOR  
         SELECT 
             g.ID, @carID, g.ShapeType  
         FROM   
             tblgeo AS g  
         WHERE  
             car_id @ID;   

    IF (SELECT CURSOR_STATUS('local','ShapeCursor')) >= -1
    BEGIN
        Print 'DEALLOCATE CURSOR'
       --DEALLOCATE ShapeCursor
    END   
    ELSE    
        Print 'ELSE OPEN CURSOR'

    OPEN ShapeCursor;

    FETCH NEXT FROM ShapeCursor INTO @ID, @CarIdx, @ShapeType;  

    WHILE (@@FETCH_STATUS = 0) 
    BEGIN

We'd need to see more of the code to be sure, since you're only showing the top part. Since you're declaring a cursor in a loop, an obvious issue is if you're not also deallocating it in each cycle of the loop. That being said, and especially since you say you're new to SQL, cursors are usually best avoided unless all other avenues have been explored and nested cursors are rarely, if ever, the right solution to the problem. If you could describe your goal, and some sample data and expected results, we may be able to offer a solution that doesn't use cursors at all. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 18, 2017 at 7:42
Cursors are evil. nested cursors are even worst. Unless you really have no other choice, don't use them at all. Having said that, if you really must use nested cursors, the inner cursor must be declared (and deallocated) outside the loop of the outer cursor. — Zohar Peled
– Zohar Peled, Commented Jan 18, 2017 at 7:43
Rewrite the query so it doesn't use cursors. Cursors are very evil. They are the most dangerous thing you can use in any database. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 18, 2017 at 9:04
What are you trying to do with this code? It's impossible to understand from this code. Whatever it is, there are simpler and faster ways to do it. For example, there are a lot of solutions to concatenate strings and SQL Server 2016 even has a STRING_AGG function — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 18, 2017 at 9:07

IVNSTN · Accepted Answer · 2017-01-18 09:01:01Z

Example with nested cursors:

DECLARE
        @crTables CURSOR,
        @crColumns CURSOR,
        @table_id INT,
        @table_name VARCHAR(100),
        @column_id INT,
        @column_name VARCHAR(100)

SET @crTables = CURSOR FAST_FORWARD FOR 
SELECT TOP 10 [object_id], NAME
FROM sys.tables t
ORDER BY t.[object_id] DESC

OPEN @crTables

FETCH NEXT FROM @crTables
INTO @table_id, @table_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @table_name + ': '

    SET @crColumns = CURSOR FAST_FORWARD FOR 
    SELECT TOP 10 c.column_id, c.name
    FROM sys.[columns] c
    WHERE c.[object_id] = @table_id
    ORDER BY c.column_id

    OPEN @crColumns

    FETCH NEXT FROM @crColumns
    INTO @column_id, @column_name

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @column_name

        FETCH NEXT FROM @crColumns
        INTO @column_id, @column_name
    END

    CLOSE @crColumns
    DEALLOCATE @crColumns

    PRINT ''

    FETCH NEXT FROM @crTables
    INTO @table_id, @table_name
END

CLOSE @crTables
DEALLOCATE @crTables

GO

Which will give the same result as:

GO

DECLARE @txt VARCHAR(MAX)

SET @txt = 
STUFF(
    (
    SELECT TOP 10
        CHAR(13) + CHAR(10) + NAME
        + (
                SELECT TOP 10 CHAR(13) + CHAR(10) + c.name
                FROM sys.[columns] c
                WHERE c.[object_id] = t.[object_id]
                ORDER BY c.column_id
                FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)')
        + CHAR(13) + CHAR(10)
    FROM sys.tables t
    ORDER BY t.[object_id] DESC
    FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)'), 1, 2, '')

PRINT @txt

Try to avoid cursors.

Collectives™ on Stack Overflow

How to use cursor within cursor in SQL Server?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related