sql-server

SQL Server Cursor vs WHILE loop


In SQL Server: when you need to loop a set of data, is there a benefit of using a SQL Server cursor. Or is using a WHILE loop (see below) just the same.

This assumes that you want to do something that cannot be done in a set-based way

I ask because the WHILE loop seems clearer and easier to understand.

-- loop through a table
DROP TABLE IF EXISTS #LoopingSet;
CREATE TABLE #LoopingSet (RowID INT IDENTITY(1,1), DatabaseName sysname);
INSERT INTO #LoopingSet (DatabaseName) SELECT [name] FROM sys.databases WHERE database_id > 4 ORDER BY name;

DECLARE @i INT = (SELECT MIN(RowID) FROM #LoopingSet);
DECLARE @n INT = (SELECT MAX(RowID) FROM #LoopingSet);
DECLARE @DatabaseName sysname = '';

WHILE (@i <= @n)
BEGIN
    SELECT @DatabaseName = DatabaseName FROM #LoopingSet WHERE RowID = @i;
    PRINT @DatabaseName; -- do something here
    SELECT @i = MIN(RowID) FROM #LoopingSet WHERE RowID > @i;
END;

Solution

  • I ask because the WHILE loop seems clearer and easier to understand.

    Is it clearer and easier to understand?

    DECLARE 
        @Name sysname,
        @Names CURSOR,
        @Rows integer;
    
    SET @Names = CURSOR FORWARD_ONLY STATIC READ_ONLY FOR
        SELECT [name] 
        FROM sys.databases 
        WHERE database_id > 4 
        ORDER BY [name] ASC;
    
    OPEN @Names;
    
    SET @Rows = @@CURSOR_ROWS;
    
    WHILE @Rows > 0
    BEGIN
        FETCH NEXT FROM @Names INTO @Name;
        PRINT @Name;
        SET @Rows -= 1;
    END;
    

    It's a subjective question but objectively, your implementation of a cursor is a little less efficient than using the built-in facility.