When to use SQL Server Cursor

We mostly use Cursors when there is a need for row by row operation, which cannot by replaced by a nested queries. The greatest disadvantage in Cursors is it stores the result set in an memory and processes one by one. Suppose for 1000,00 records it occupies huge memory and performance is will very poor. In those cases it is always better to go for While Loop instead of a cursor. This improves the performance drastically.

declare @loopvar int
declare @maxcnt int
declare @cnter int
declare @outermax int
create table #temptable (
ROWID INT IDENTITY(1,1),
productgp int )

Insert into #temptableselect distinct id from products where status = 1 order by ID
set @outermax = @@ROWCOUNT
set @cnter = 1
While (@cnter <= @outermax)
Begin
// Do your process here
// Do your process here
set @cnter = @cnter +1
end

Use Cursors only if resultset is less in number, say less than 5000 records.