How to use Cursors in SQL Server
Cursors are a useful tool to move through a recordset within a stored procedure in many DataBase Management Systems (D.B.M.S.) such as SQL Server and Oracle. How they are implemented can be different, Oracle and SQL Server cursors are similar but you couldn't run one on the other without tweaking. They can also have a downside as being efficient and slow. Efficiency and a different way to do things will be considered in a different blog article but now, lets see how to use them.
create table solarplanets (id int identity(1,1),name varchar(20))
Insert into solarplanets values ('Mercury');
Insert into solarplanets values ('Venus');
Insert into solarplanets values ('Earth');
Insert into solarplanets values ('Mars');
Insert into solarplanets values ('Ceres');
Insert into solarplanets values ('Jupiter');
Insert into solarplanets values ('Saturn');
Insert into solarplanets values ('Uranus');
Insert into solarplanets values ('Neptune');
Insert into solarplanets values ('Pluto');
Create table solarmoons (id int identity(1,1), planet varchar(20), name varchar(20))
Insert into solarmoons values ('Earth','Moon')
Insert into solarmoons values ('Mars','Phobos')
Insert into solarmoons values ('Mars','Deimos')
We'll use the above tables for our examples. Below is an example of a simple single Cursor.
declare @Name varchar(20)
declare @Moon varchar(20)-- Used in the nested example.
declare crs cursor for Select name from Solarplanets
open crs
fetch next from crs into @Name
while @@fetch_status = 0
begin
delete from solarplanets where name in ('Ceres','Pluto')
print @Name
fetch next from crs into @Name
end
close crs
deallocate crs
A couple of things you will notice and are correct.
- A cursor variable which in this case is crs doesn't need a @ in front of it. If you do prefix it with an @ sign, it will not work.
- To begin a cursor, you only need to open it but once you've processed all, you have to close and deallocate.
- Cursors can be used to update other records but you'll need to use the update command as you would normally do so.
- Don't forget the "fetch next" inside the while loop otherwise it will never end.
- If you delete records in the selected records that are to be accessed with the cursor and have not been accessed yet, they will still exist. To prevent unnecessary processing, you will need to check if the record exists using the folllowing for instance.
if(Select count(*) from solarplanets where name = @Name) = 1
print @Name
Nested Cursors
It is possible to have nested cursors so one cursor can react to the other. To see a nested cursor, add the following code after
Print @Name
in the example above. if(Select count(*) from solarmoons where planet = @name) > 0
Begin
declare mooncrs cursor for select name from solarmoons where planet = @name
open mooncrs
fetch next from mooncrs into @Moon
While @@FETCH_STATUS = 0
Begin
Print ' ' +@Moon
fetch next from mooncrs into @Moon
End
close mooncrs
deallocate mooncrs
End
Last Modified : June 2023