Navigating Table Without Using Cursor in SQL Server

Hello to everyone,

In this article, I will give information about navigating the table without using Cursor in SQL Server.

In most cases, most people prefer to use Cursor to navigate the table in SQL Server, but it is a very costly operation.

Of course, Cursor is used where Cursor should be used. Using Cursor in every operation puts extra load on SQL Server.

In the following example, you will learn how to navigate the table with and without Cursor.

I made the examples using the Northwind database.

Our first example is about Cursor.

DECLARE @ProductID INT;
DECLARE @ProductName NVARCHAR(50);

DECLARE Cur_Example CURSOR FAST_FORWARD FOR
SELECT ProductID
FROM Products;

OPEN Cur_Example;
FETCH NEXT FROM Cur_Example
INTO @ProductID;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @ProductName = ProductName
    FROM Products
    WHERE ProductID = @ProductID;

    PRINT 'Product Name : ' + @ProductName;

    FETCH NEXT FROM Cur_Example
    INTO @ProductID;
END;

CLOSE Cur_Example;
DEALLOCATE Cur_Example;
GO

When you run the above query, you will see the following result.

Our second example is about the While loop.

DECLARE @RecordCount INT;
DECLARE @ProductID INT = 1;
DECLARE @ProductName NVARCHAR(50);

SELECT @RecordCount = COUNT(*)
FROM dbo.Products;

WHILE @ProductID <= @RecordCount
BEGIN
    SELECT @ProductName = ProductName
    FROM dbo.Products
    WHERE ProductID = @ProductID;
    PRINT 'Product Name : ' + @ProductName;
    SET @ProductID += 1;
END;
GO

When you run the above query, you will see the following result.

As you can see, you can move around the table without using the Cursor. Its query is shorter than Cursor. Also, while loop is faster to complete the operation than Cursor.

Good luck to everyone in business and life.