Programming Examples

Are you a Programmer or Application Developer or a DBA? Take a cup of coffee, sit back and spend few minutes here :)

SQL Cursor Explained

1. About SQL Cursor

Let us say in a SQL Script, in Stored Procedure, or in a function, we want to process the data record-by-record. In this case, we can go for a SQL Cursor. Note, using Cursor is not advisable as it eats up memory, network bandwidth and performs lock while processing the records. So, before writing a cursor think twice and use it when no other options are available. With this overhead, we will get full control over the record processing. In this example, we will create a SQL cursor to fetch record from the Jobs table of the pubs database and list the records. In real world, after reading the record, there will be business logic that performs various other actions based on the fetched data.

2. Variable to Hold Cursor Fetch Results

First, we must declare variables to hold the data read from the records. The data type of the variables should match with the data type of the record columns. Now look at the below code:

Fig 1. Declare Variables for the Fetch
Fig 1. Declare Variables for the Fetch

Explanation

  1. The NoCount variable is turned ON. This means when a query is executed affected row counts are not emitted as a message.
  2. Here, we declare three variables for the SQL Script execution. Each variable will be mapped to a column in the Jobs table of the Pubs database. We will see that soon.

3. Open SQL Cursor & Do Initial Fetch

An SQL Cursor works on four sequences of operation namely declare, open, fetch and close. The below code opens a cursor and performs a single fetch:

Fig 2. Open Read Only Cursor and Do initial fetch
Fig 2. Open Read Only Cursor and Do initial fetch

Explanation

  1. We named the SQL Cursor as JobCursor which we marked as Forward_Only and Read_Only. The option Read_Only implies that we cannot change the data. The option Forward_Only states that cursor pointer moves only in forward directions. This mean, once the record is read, it cannot be read again with the same cursor wihtout reopening it.
  2. The select statement here decides the record set which the cursor will read one by one for processing.
  3. Cursor’s Open statement is followed by the Cursor name. It opens the cursor for fetching the records one by one.
  4. Fetch Next From will fetch the first record from the RecordSet. The into clause tells where to keep the record set columns. In our case, we opened the cursor to read three columns from the Jobs table. So, we provide three mapped variables in the Into Clause. After executing this statement, all these three variables hold the relevant data from the first record. When query returns zero records, fetch statement does not populate anything into these variables.

4. Iterate Through SQL Cursor

In the previous section we did our initial fetch. But, when the cursor returns more than one row, we need to iterate them one-by-one. Now look at the below code:

Fig 3. Iterate and fetch the record data into variables
Fig 3. Iterate and fetch the record data into variables

Explanation

  1. The @@Fetch_Status is a system function which will hold a value of Zero when the fetch was successful. A negative number indicates fetch failed because of various reason. Here, we form a while loop which quits when the fetch fails. Note, the cursor pointer advances in each Fetch Next statement and after some point in time, it goes beyond the last record @@Fetch_Status holds a value of -1.
  2. Once we are inside the loop, we can make use the variables to form the business logic. In our case, we simply printed the fetched data.
  3. After processing a record, we make a call to Fetch Next to point the cursor to the next row. If there is no next row, we come out of the loop as @@FETCH_STATUS will not be 0.

5. Cursor Cleanup

After doing all the required process, we can close the Cursor and release the consumed resources. Have a look at the code below:

Fig 4. Close and Deallocate Cursor
Fig 4. Close and Deallocate Cursor

Explanation

  1. Using Close statement, we can close the cursor. But, later, we can reopen it as the cursor definition is retained.
  2. The Deallocate statement releases all the resources associated to the Cursor. If we do not want to use the cursor again in the running context (Script), we can deallocate it.

6. Code Reference

Categories: SQL Server

1 reply

  1. Excellent work! Thanks for clarifying cursors

Do you like this Example? Please comment about it for others!!

This site uses Akismet to reduce spam. Learn how your comment data is processed.