SQL Cursors

SQL cursors, for those who don't know, are effectively a way of introducing a loop into a chunk of SQL code. Quite why the people who invented SQL couldn't call them "loops" I don't know - DBAs always have to be different, I suppose. Anyway it's a handy thing to know.

Beware though that SQL cursors are resource-intensive. If efficiency at the database end is important to you, it might be better to return the data and loop through it in the handling code. However, there are times when you need to get data, loop through it and then do something else at the DB end with the results of the loop. In those cases using a cursor is likely to be less intensive than the round trips involved in two separate back-and-forth trips to the database server.


Declare c1 cursor read_only
For
Select from
-- select statement picks up
--rows to sort through

Declare @trackingID int
-- declare a variable to hold the primary key
-- of the rows you’re returning

Open c1

Fetch next from c1 into @trackingID

While @@fetch_status = 0
Begin
--do something with the tracking variable,
--such as using it to select some
--data or do an update or somesuch

Fetch next from c1 into @trackingID
End

Close c1
Deallocate c1