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.

Navigating Table Without Using Cursor in SQL Server

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.

Navigating Table Without Using Cursor in SQL Server

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.

694 Views

Yavuz Selim Kart

I try to explain what I know in software and database. I am still improving myself by doing research on many programming languages. Apart from these, I am also interested in Graphic Design and Wordpress. I also have knowledge about SEO and Social media management. In short, I am a determined person who likes to work hard.

You may also like...

Don`t copy text!