Using a T-SQL Cursor in SQL Server

I can never seem to remember the syntax for a cursor in T-SQL so I decided to add it to my blog. If you are not familiar with a cursor, it is essentially a way to loop through selected rows in a SQL database.
Please use caution with cursors. They are very poor performers and often other options make more sense for performance reasons. However, there are times when it makes sense to use cursors. It can be difficult for programmers to think like DBAs sometimes because programming is typically a step-by-step logical progression of thought, but SQL databases should be thought of in terms of sets. Cursors are in a gray area that kind of abuses SQL a little bit because that is not what databases are optimized for.
There is my two cents. Assuming you need to use cursors here is how they work. Basically a cursor takes a query, and copies each column in that select statement into T-SQL variables. You can then do whatever you want with these variables. In our case we will send email. Once you are done with that row you get the next row.
In general you want to use the where clause to limit the rows that the cursor has to go through. For example, do NOT do something like (select * from Person) and then in the cursor use an if statement to ignore all records that don't meet your criteria. This is best done in a where clause.
Let's assume you have a query like the following:

Select fname, lname, email from Person where city = 'Phoenix' order by fname

Now you want to send an email to each of these people. Here is what you do:

1. Declare variables for each of the columns you are select Declare @FName as nvarchar(50)
   Declare @LName as nvarchar(100)
   Declare @Email as nvarchar(255)
2. Declare the Cursor Declare PersonCursor CURSOR FAST_FORWARD FOR
3. Now your we use the select statement (the one you want to loop through) Select fname, lname, email from Person where city = 'Phoenix' order by fname
4. Open the cursor and copy the columns into the T-SQL variables. Be sure to order the select statement columns and the columns below in the same order since the order determines how the values are mapped.
   OPEN PersonCursor FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email
5. Now that we have the data for the first row, let's start the WHILE loop. Notice, that we get the first row BEFORE the WHILE loop is started so that the loop condition will be satisfied to start with. To control when the WHILE loop will exit we use the @@FETCH_STATUS built-in T-SQL variable. Notice that at the end of the WHILE loop we use exact same FETCH lines as we did to get the first row of data.
   WHILE @@FETCH_STATUS = 0
   BEGIN
      -- do row specific stuff here
      print 'FName: ' + @FName print 'LName: ' + @LName print 'Email: ' + @Email
      FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email
   END
6. The most important lines are the next two lines. These are the lines that free the resources. If you don't do this you have what they call a memory leak. It can eat up valuable server memory and possibly eventually take down your server if you don't clean up after using resources.
   CLOSE PersonCursor
   DEALLOCATE PersonCursor

Here is the completed example in one big block.
Declare @FName as nvarchar(50)
Declare @LName as nvarchar(100)
Declare @Email as nvarchar(255)
Declare PersonCursor CURSOR FAST_FORWARD FOR 
Select fname, lname, email from Person where city = 'Phoenix' order by fname
OPEN PersonCursor
FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email 
WHILE @@FETCH_STATUS = 0
BEGIN
   -- do row specific stuff here
   print 'FName: ' + @FName print 'LName: ' + @LName print 'Email: ' + @Email
   FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email
END 
CLOSE PersonCursor
DEALLOCATE PersonCursor