SQL Cursors

In SQL, a cursor lets you process data on a row by row basis. As we’ve seen, SQL is really good at manipulating data sets. We can group many rows together, update many rows at the same time, delete many rows at the same time, etc. However, instead of performing a single action on a set of rows we might want to loop through a list of rows and process along the way.

Syntax

Below is the general syntax for a cursor:

DECLARE @AuthorID char(11)

DECLARE c1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors

OPEN c1

FETCH NEXT FROM c1
INTO @AuthorID

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @AuthorID

FETCH NEXT FROM c1
INTO @AuthorID

END

CLOSE c1
DEALLOCATE c1

The first DECLARE is defining a variable (@AuthorID). Variables have to begin with @ and can be given a value in the declaration statement. An example of this would be:

DECLARE @AuthorID char(11) = abc123def45

The next DECLARE is declaring the cursor. This cursor has the name c1 and will be read only. This is important - if we are only going to be reading data, we want to be sure to set the cursor as read_only. This will help us imporve performance. The SELECT statement below it is the process that we want to use to loop through every row. Note you must have the FOR statement before the SELECT statement. Next, the OPEN statement executes the cursor’s SELECT statement and puts the result in a set. The FETCH command returns one row from the result and puts it into the @AuthorID variable. We could fetch multiple columns and put them into multiple variables if we wanted (assuming we selected multiple in the cursor definition, of course!). Next, the line

WHILE @@FETCH_STATUS = 0

tells SQL that we want to continue doing this until there are no more rows. In other words, FETCH_STATUS will be 0 until there are no more rows. Next, the lines within the BEGIN/END will loop through the @AuthorID variable and print each @AuthorID. Obviously, there are much more efficient ways to do this within SQL. This example is just a basic one to get an idea of the syntax. In general, if you can avoid using a cursor, you probably should because they can be so slow. Have questions or suggestions? Please feel free to comment below or contact me.