s

Home > mssql > cursor to loop through records in mssql database

cursor to loop through records in mssql database edit button Edit

author
Murugan Andezuthu Dharmaratnam | calendar 12 December 2023 | 584

While working with a SQL database you want to create a cursor to loop through records in a table. Here is an example for the same. lets loop though a table named Country with columns Name and ModifiedBy, the sample code below can help you get started.

Code

declare @Name nvarchar(max)
declare @ModifiedBy nvarchar(max)

declare mycursor cursor for 
select [Name], [ModifiedBy] from [dbo].[Setup.Country]

open mycursor

FETCH NEXT FROM mycursor INTO @Name, @ModifiedBy

WHILE @@FETCH_STATUS = 0
BEGIN
	select @Name, @ModifiedBy
	FETCH NEXT FROM mycursor INTO @Name, @ModifiedBy
END 

CLOSE mycursor ;
DEALLOCATE mycursor ;

Cursors are best used when you absolutely need row-by-row processing. Cursors can be resource-intensive and may not be the most efficient way to process large amount of data. If possible, try to use set-based operations in SQL for better performance.