T-SQL Ninja #20

CURSOR

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…

References

Ninja-Notebooks @ GitHub

Leave a Reply

Your email address will not be published. Required fields are marked *