Cursors are a slightly more difficult concept in SQL, but mainly because they are not recommended by most database developers due to their high resource usage. Cursors let you create loops in your stored procedures, so you can evaluate data record-by-record. Think of cursors as a stored data set that then lets you go through each record, manipulate or view a field, and then perform some kind of logic against the record based on a field's value. In most cases, you don't need a cursor in your code, but it's good to understand cursor syntax and how it functions differently from other SQL code.
Cursor Components
Let's take a look at some cursor code. The following cursor is called "mycursor" and it loops through a set of customer records.
DECLARE mycursor CURSOR FOR
SELECT CustomerId FROM Customer
WHERE state='tx'
OPEN mycursor FETCH NEXT FROM mycursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @id
END
CLOSE mycursor
DEALLOCATE mycursor
That's quite a bit more code in a procedure than we've previously used. We've cut out some of the stored procedure statement and focused on just the cursor code.
The first section of code declares the cursor. In this example, the cursor's name is "mycursor" and the data set is a SELET statement that gets a list of customer ID fields where the customer is located in Texas. The SQL engine allocates memory for the cursor and populates it with the data set you define in the SELECT statement.
The next section of the CURSOR statement is the OPEN keyword. The OPEN statement signifies that you're ready to start processing the data in the cursor. If you don't use an OPEN statement, the stored procedure or SQL statement will fail and your database gives you an error.
If you have any other programming experience, the FETCH statement is the part of the statement that gets your next record from the cursor's data set. FETCH is similar to the beginning of a FOR loop that grabs the current record to prepare it for your SQL conditions and manipulation of your data. The INTO keyword places the fields into a SQL variable @id. In this example, only one field, the CustomerId field, is sent to the cursor. If you had two columns returned, you'd need two columns. Columns are assigned a variable in the same order they are retrieved in your initial SELECT statement.
Next is the WHILE statement. The @@ variable definition hasn't been seen before until now. The @@ declaration indicates that you're using a SQL system variable. The @@FETCH_STATUS system variable is an internal SQL reference to the current status of your fetch. If there are no more records, then you have no more records to process and the WHILE statement will terminate. Without the fetch statement, you create an infinite loop. An infinite loop is a common programming error where the main loop condition is never met. If the WHILE statement is never met, then there is never an ending to the loop. The bug eats up memory and can crash your database server. An infinite loop must be terminated and stopped from your database administrator.
In this example cursor statement, the SQL database just prints the customer's ID. You will probably want to perform some other action on your data, but this cursor statement is just an example of how you can use a cursor to manipulate data. The result is a printout of your customer IDs in Texas. You'll see the results in your SQL editor.
After you complete your data processing, you need to clean up memory resources, which are taken by the cursor. A cursor can have thousands and even millions of records allocated. You can imagine the amount of server resources needed to store a cursor with millions of records, which is why a database administrator avoids using cursors.
To ensure that the cursor doesn't bleed memory from your server, you must then close the cursor and deallocate memory usage. You can see how to clean up resources in the above statement using the CLOSE and DEALLOCATE cursor statements.
You can put a cursor in a stored procedure to run it just by using a procedure name. The following SQL code creates a stored procedure named "FetchCursor" in your database.
CREATE PROC FetchCursor
(
@state varchar(2)
)
AS
DECLARE mycursor CURSOR FOR
SELECT CustomerId FROM Customer
WHERE state= @state
OPEN mycursor FETCH NEXT FROM mycursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @id
END
CLOSE mycursor
DEALLOCATE mycursor
The above statement puts your cursor in a stored procedure and creates dynamic code, so you can pass the cursor a state instead of making the state statically Texas.
The following code would then print each customer with a state value of Texas.
EXEC FetchCursor ‘tx'
Considerations when Using a Cursor
Cursors are generally not a needed option when creating your reports and programs. Normally, you can satisfy a business requirement with a WHERE clause or some conditional statements before retrieving your records. The result is a faster program and more efficient data processing, especially when you deal with millions of records at a time. Cursors are sometimes used to process data on the fly as a one-time deal for a developer. In other words, you use it once and then create a more efficient procedure later.
If you absolutely must have a cursor, then you should design it well. Limit the number of records returned by the procedure. If you can reduce the number of records processed, you'll speed up your reports by several seconds (sometimes even faster when you have long-running reports).
Fine tune your cursor queries and always test them first. The SELECT statement that loads your data into the cursor's variables should be tuned, so that it performs well. For instance, when you JOIN a table, you should use your JOIN statement on columns that contain indexes. Indexed columns greatly improve the time needed to collect data on multiple table resources. As a matter of fact, a SELECT statement that JOINs on a table column with no index can take several minutes longer than columns with indexes.
Unless you get into some heavy reporting, you'll likely avoid using cursors. Cursors are used in reporting environments where the data is ported over to a reporting server, so the affects of any server resources aren't felt in the production environment.
