Every now and then the need to write a SQL Server cursor comes up. Since I find myself going back through a labyrinth of code when this happens, I thought it would be a good idea to post it here. That's if I remember to look here the next time I need to create one - which just might be several seasons later.
*The humorous thing about this is that as I went to SQL Server Management Studio to copy the cursor example, it was gone. Apparently, I closed the script window immediately after running it thinking I wouldn't need to use this in a long time. No worries though, I wrote this up again in a short time.
declare @SOMEID int
declare appcursor cursor FAST_FORWARD
FOR SELECT Table1ID FROM tblTable1
open appcursor
fetch next from appcursor into @SOMEID
while @@fetch_status=0
begin
delete
from tblWhatever
where WhateverID = (
select top 1 WhateverIDFK
from tblSomewhere
where SomeID = @SOMEID
order by DateOfEntry
)
fetch next from appcursor into @SOMEID
end
close appcursor
deallocate appcursor
One thing to note is the FAST_FORWARD hint. You should always declare this when you need a forward-only, read-only result set. FAST_FORWARD allows you to traverse the dataset with the least amount of overhead. Cursors are generally slow because of the nature of their use. Most times, you are forced to use a cursor because you have seemingly no other way to perform certain database actions, which frequently require small units of work like in the example above. In the case where you need to update a result set, consider using FORWARD_ONLY (only use fetch next). Please see this MSDN article for more information on cursor types.
