SQL query - Iterate through table records without using cursor

In this article we will see ow to loop through the rows of a table without using cursor

In this article I will explain how to loop through each rows in a table and get the data one by one for processing.

-- Create a table variable to store user data
DECLARE @myTable TABLE
(
    UserID INT IDENTITY(1,1),
    UserName VARCHAR(50),
    Password VARCHAR(50),
    Email VARCHAR(50)
)

-- Insert some data to table to work on that data

INSERT INTO @myTable(UserName, Password, Email)
VALUES ('Jack', 'JackPwd', 'jack@gmail.com')

INSERT INTO @myTable(UserName, Password, Email)
VALUES ('Raj', 'RajPwd', 'raj@gmail.com')

INSERT INTO @myTable(UserName, Password, Email)
VALUES ('smith', 'smithPwd', 'smith@gmail.com')

INSERT INTO @myTable(UserName, Password, Email)
VALUES ('Tom', 'tomPwd', 'tom@gmail.com')

-- Get the number of rows in the looping table
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(UserID) FROM @myTable) 

-- Declare an iterator
DECLARE @I INT
-- Initialize the iterator
SET @I = 1

-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN
        -- Declare variables to hold the data which we get after looping each record
        DECLARE @iUserName VARCHAR(50), @iPassword VARCHAR(50), @iEmail VARCHAR(50)    
        
        -- Get the data from table and set to variables
        SELECT @iUserName = UserName, @iPassword = Password, @iEmail = Email FROM @myTable WHERE UserID = @I
        -- Display the looped data
        PRINT 'Row No = ' + CONVERT(VARCHAR(2), @I)
        PRINT 'UserName = ' + @iUserName + ', Password = ' + @iPassword + ' Email = '+ @iEmail
        -- Increment the iterator
        SET @I = @I  + 1
END


Here is the output of the above query.

Row No = 1
UserName = Jack, Password = JackPwd Email = jack@gmail.com
Row No = 2
UserName = Raj, Password = RajPwd Email = raj@gmail.com
Row No = 3
UserName = smith, Password = smithPwd Email = smith@gmail.com
Row No = 4
UserName = Tom, Password = tomPwd Email = tom@gmail.com