Loop through table rows table without a cursor

Learn to loop through the rows of a table without using SQL Server cursor.

A TABLE variable is used to store the query results and a simple WHILE LOOP is used to iterate the rows of the resultset.   This is what enables you to avoid the use of a SQL Server CURSOR.

-- 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

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

By Cos mos   Popularity  (61589 Views)