SQL Server Reference Guide

Design Elements Part 12: Loops

Last updated Mar 28, 2003.

Modern programming languages use multiple types of loops, each with its advantages and appropriate uses. Transact-SQL is a bit more limited, with only one or two true looping commands. The reason is pretty straightforward - looping activities in a database have to do with data, not normally program logic. In today's tutorial, I’ll examine a couple of simple loops, and then move on to the row operations in which SQL Server has a little more advantage.

For the true looping code, there's really only one command in T-SQL, called WHILE. Here's an example:

 /* First Loop Example */ 
DECLARE @a INT 
SET @a = 1
WHILE @a < 11
  BEGIN
   SELECT @a
   SET @a = @a + 1
  END
PRINT 'All Done' 

Notice that I've declared an integer variable in the first line, and set it to 1 in the next. This is nothing you haven't probably seen before in any beginning programming book.

In the WHILE command in the third line, I tell the computer to perform the block below while the variable of @a is less than 11. I have a BEGIN and END block set up, and between them lie the commands to run for the duration of the WHILE logic. In this simple example, I've merely selected the value to the screen.

The important part is to increment the value of the variable by 1, so that it will eventually reach 11, and then get out of the loop. I selected 11 for the WHILE condition, because I want the system to count to 10. Without incrementing, I'd never reach 11, and I'd never exit the loop. SQL Server is really not especially good at breaking out of loops; do one of them wrong, and it may be necessary to kill the ID of the process that is running it. 

This is a simple loop, but the logic holds for more complex work. You can nest these kinds of loops, but more than three or four loops become inefficient. As a matter of fact, if you end up with more than three or four, it's time to examine the code to see if the goal can be accomplished in another way.

There's another way to do a loop, but it's not technically the same thing. In the next example, I’ll explain another T-SQL construct called a label. A label is simply a marker for a block of code. Here's the example:

 /* Second Loop Example */
DECLARE @b INT
SET @b = 1
test_loop:
 BEGIN
  SELECT @b
  SET @b = @b + 1 
 END
IF @b < 11 GOTO test_loop
GO 

This type of GOTO-based looping is similar to old BASIC code, and I haven't seen a lot of it in use recently.

Those are the only true loops you have in T-SQL. Other database platforms implement more looping keywords, but in reality they aren't often necessary. Most looping tasks are done using other development languages, and the kind of repetitive task we're after in T-SQL is often not really a loop at all.

What that means is that I often want to pull in some data, and work with it a row at a time. You might think of this as a loop, but it's actually something called a row-based set. T-SQL is normally data set-based, not row based, and so I think of these row-based operations as a kind of loop.

OK, back to Transact-SQL — to handle row-based operations, T-SQL provides a construct call a Cursor. Cursors are just row-based operation commands, and they aren't difficult to learn at all. Here's the basic outline for creating a cursor:

• Declare the cursor and fill it
• Open it
• Fetch from it
• Close the cursor and deallocate it

Cursors' purpose will become more apparent as I show you a few. I'll begin this topic in this tutorial, but it has so many facets that you'll see it again in future installments. Here, I’ll focus more on creating and accessing the cursor. In future tutorials, I'll put them to more complex use.

Let me step through the process of creating a simple cursor. First, I declare the cursor, much like the variables you've seen before, and then I’ll fill it with some data:

DECLARE test_cursor CURSOR
FOR SELECT * FROM authors

Pretty simple, but it gets a bit deeper from here. There are many options available, in just this first part of the cursor, so you can just pause here to look at those options. Don't worry if they seem a bit confusing at the moment; just read through them and you'll apply them later.

First, the cursor's name is how I’ll refer to it throughout the process. The name can have from 1 to 128 characters.

The first option on the DECLARE name CURSOR is LOCAL. This option specifies that the cursor is available only in the batch, stored procedure, or trigger in which the cursor was created. The LOCAL cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates. (This saves you a step, later on.)

The alternate to LOCAL is GLOBAL. If you set the cursor to GLOBAL, the cursor would available to the entire connection, not just its batch of code. The GLOBAL cursor is implicitly deallocated at disconnect.

The next set of options deal with how data is streamed from the server. FORWARD_ONLY specifies that the cursor will only fetch data sequentially from the first to the last row. If you use this option, then FETCH NEXT is the only fetch option supported (you’ll see more about this in a moment).

The option with the most relevance to data integrity is STATIC. This option means that the cursor will use a temporary copy of the data instead of the base tables. When set up this way, the cursor does not allow modifications; modifications made to base tables are not reflected in the data returned by fetches made to this cursor. This is sort of a "dirty read," since it may not reflect up-to-the-moment data changes. Use with caution!

The KEYSET option isn't used a great deal, but can be quite important. KEYSET ensures that the cursor uses the set of keys that uniquely identify the cursor's rows (keyset), so that the membership and the order of rows in the cursor are fixed when the cursor is opened. The KEYSET cursor keeps updates to non-key values from being made through this cursor. One side effect is that inserts made by other connections are not always visible.

The DYNAMIC option sets the cursor to reflect all data changes made to the base tables as you scroll through the cursor. The side effect here is that you can't use the FETCH ABSOLUTE option if you use a DYNAMIC cursor.

Here's an option you’ll see a lot more of: FAST_FORWARD. This type of cursor will be a FORWARD_ONLY and a READ_ONLY cursor. FAST_FORWARD cursors produce the least amount of overhead on SQL Server, but allow potential dirty reads.

Similar to FAST_FORWARD, a READ ONLY option means that the cursor cannot be updated.

If you're after a little heavier hand, the SCROLL_LOCKS option will lock the rows as they are read into the cursor to ensure that updates or deletes made through the cursor will succeed.

Another popular option, OPTIMISTIC, doesn't lock rows as they are read into the cursor. Instead, the updates or deletes made through the cursor will not succeed if the row is updated outside the cursor.

You probably won't run into the TYPE_WARNING option often. This option means that a warning message should be sent to the client if the cursor is converted from the requested type to another.

Those are most of the options on the DECLARE name CURSOR. In the SELECT statement, the primary things to know about a cursor are that the T-SQL cannot contain the COMPUTE, COMPUTE BY, FOR BROWSE, and INTO keywords.

The final option is UPDATE [OF column_name [,...n]]. This option means that all the cursor's columns can be updated, or only the columns listed in the OF column_name [,...n] list will allow modifications.

So those are the primary options I’ll use. Next, I open the cursor to work with it:

OPEN test_cursor 

I "open" a cursor to work with the data. That action makes the contents of the cursor the current set we're working with.

Finally, I get the data and work with it:

FETCH NEXT FROM test_cursor 

This is the row-based bit I’ve been looking for. I work with the cursor one line at a time. Here's the code all in one place, with the results:

 DECLARE test_cursor CURSOR
FOR SELECT * FROM authors
OPEN test_cursor
FETCH NEXT FROM test_cursor
FETCH NEXT FROM test_cursor
FETCH NEXT FROM test_cursor
DEALLOCATE test_cursor
-----------------------------------
au_id  au_lname         au_fname    phone  address         city     state zip contract 
----------- ---------------------------------------- -------------------- ------------ ---------------------------------------- -------------------- ----- ----- -------- 
172-32-1176 White         Johnson    408 496-7223 10932 Bigge Rd.       Menlo Park   CA 94025 1

(1 row(s) affected)

au_id  au_lname         au_fname    phone  address         city     state zip contract 
----------- ---------------------------------------- -------------------- ------------ ---------------------------------------- -------------------- ----- ----- -------- 
213-46-8915 Green         Marjorie    415 986-7020 309 63rd St. #411      Oakland    CA 94618 1

(1 row(s) affected)

au_id  au_lname         au_fname    phone  address         city     state zip contract 
----------- ---------------------------------------- -------------------- ------------ ---------------------------------------- -------------------- ----- ----- -------- 
238-95-7766 Carson         Cheryl    415 548-7723 589 Darwin Ln.       Berkeley    CA 94705 1

(1 row(s) affected)

I FETCHed three lines, so I can see the iteration. Notice I added one more line; the cleanup bit is DEALLOCATE.

There’s actually another way to work over the same set of data, introduced later in SQL Server 2005’s Transact-SQL. The feature is called a Common Table Expression, or CTE. A CTE is a way of setting some data aside in memory (from a query), and then being able to work over that set of data, similar to a table variable.

Where this construct becomes more as a looping function is when it is used recursively - a process where a function can work on the function itself. Since this is a larger topic, I’ll leave it for another article, but there are two references (here: http://msdn.microsoft.com/en-us/library/ms190766.aspx and here: http://msdn.microsoft.com/en-us/library/ms186243.aspx) that you can use in the meantime. CTE’s are one of the most powerful ways to work over a set of data, but are not as suited to classical looping and incrementing functionality.