Dotnet Stuff

At times you may need to sequentially loop through some specific result set for performing some action on individual record at a time. Using SQL cursor is a choice.

But, for optimal performance and to avoid potential problems, as a recommended best practice, cursors should be avoided as much as possible in T-SQL code.

But we have other choices as well. Use following sample logic to loop through table rows in SQL code.

How To Loop Through Table Rows Without Cursor In SQL Server?

Consider an Emp table with employee details. Lets say we need to do some process for each employee record by looping through the table row using T-SQL code without using a cursor.

Example, each employee’s salary processed status to be updated.

Initially all the employees have SalProcessed field set with 0.

Loop Through Table Rows With T-SQL Cursor

Lets first see, how to do this with an sql cursor.

Declare @EmpID int;
Declare @EmpName nvarchar(50);
Declare @SalProcessed int;
 
Declare Cur_Employees Cursor FAST_FORWARD For
   Select EmpID From Emp
 
Open Cur_Employees
Fetch Next From Cur_Employees INTO @EmpID
  
While @@FETCH_STATUS = 0

Begin 
   Update Emp Set SalProcessed = 1 Where EmpID = @EmpID 
   Fetch Next From Cur_Employees Into @EmpID
End
 
Close Cur_Employees
Deallocate Cur_Employees
GO

Example 1 – Loop Through Table Rows in SQL without Cursor

Select records where SalProcessed = 0 and process the logic.

Set the SalProcessed=1
Continue loop Until count = 1

Declare @Id int
While (Select Count(1) From Emp Where SalProcessed = 0) > 0
Begin
Select Top 1 @Id = EmpId From Emp Where SalProcessed = 0
--Do the process of individual record here
Update Emp Set SalProcessed = 1 Where Id = @Id
End

Example 2 – Loop Through Table Rows in SQL Without Cursor :

Declare @Records INT= 0,
@Id int= 1,
@EmpName nvarchar(70)
-- Fetch total number of records to loop
Select @Records = Count(1) From Emp

While(@Id <= @Records)
Begin
-- select the required columns

Select@EmpName = EmpName From
(Select ROW_NUMBER() over (Order By EmpID asc) as RowNumber,
  EmpID,
  EmpName
  From Emp
) TempTbl
Where TempTbl.RowNumber = @Id
-- Process it here
Print @EmpName

-- increment Id by 1
@Id = @Id + 1
End

Summary

This article would have given you ideas on how to use a SELECT statement to loop through a set of records without using cursor to process one record at a time.
You can use Cursor also for looping through the record-set in T-SQL, but with select, it is simpler to code. You need to choose wisely among the two as per your requirement. Share your comments below.