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.
Table of Contents
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
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.
Leave a Reply