What are CURSORs?
In the last two weeks you had learned about the WHILE statement in the Dojo and used it to delete batches of lines. We had given you the warning that the SQL Server (like almost every relational database engine) is primarily designed to operate with data sets, i.e. with multiple rows. But what if you really need to process individual rows in a result set for some reason? Some of our WHILE constructs have already done just that, and some of you will have flinched and instinctively closed the page. If that’s you, you can do that for this week, because today we’ll be covering CURSOR. A CURSOR can be thought of as a pointer pointing to the first row of the result set, which you can always jump to the next row.
How can you work with CURSORs?
To define a cursor, you must first define a variable that holds the line. You do this by declaring a variable that is your SELECT statement as type CURSOR FOR. So let’s first write a statement that gives us a result set that we can iterate on. For example, let’s take the set of databases on our server:
USE master; SELECT [name] FROM dbo.sysdatabases
Now we want to iterate over this result set and perform a meaningful operation for each database. We did something similar two weeks ago when we wanted to iterate over all indexes of a database. At that time we had always selected the first row with TOP(1) and deleted it from the result set. Today we do it differently and define a CURSOR to iterate over the result set. In concrete terms, this could look like the following:
DECLARE my_cursor CURSOR FOR SELECT [name] FROM dbo.sysdatabases
Now we have to execute the query and point the cursor to the first row of the result set, for this we first define a variable that holds the result:
DECLARE @name varchar(255)
Next, we can open the cursor and write the next line in the variable (if your select contains several columns, you can map each to a separate variable)
OPEN my_cursor FETCH NEXT FROM my_cursor INTO @name
To finally read one line after the other into the variable, we use a WHILE loop again. The loop condition is that one more line could be read from the cursor. This looks like the following:
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM my_cursor INTO @name
END
When you are done with this, you must first close the CURSOR, i.e. make sure that it no longer points to a result set, and then delete it, which is done with the DEALLOCATE command:
CLOSE my_cursor DEALLOCATE my_cursor
In the loop you can now do what you want with the database name stored in @name. For example, you can make backups of all databases. The whole code would look like this:
DECLARE @name varchar(255)
DECLARE @backuppath varchar(255)
DECLARE @backupfullfile varchar(510)
SET @backuppath = '~/'
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @backupfullfile = @backuppath+@name+'.bak'
BACKUP DATABASE @name TO DISK = @backupfullfile
FETCH NEXT FROM my_cursor INTO @name
END
CLOSE my_cursor
DEALLOCATE my_cursor
For safety’s sake, let me warn you again: unlike a Katana, a CURSOR is a double-edged sword. On the one hand, it allows you to do many things more than once, especially in the area of database maintenance, and on the other hand this line-by-line approach is not an efficient way to work in relational database systems. But if you are aware of this, you can use CURSORs to automate many of your daily tasks…
