SQL Server CURSOR

Summary: in this tutorial, you will learn how to use the SQL Server cursor to process a result set, one row at a time.

SQL works based on set e.g., SELECT statement returns a set of rows which is called a result set. However, sometimes, you may want to process a data set on a row by row basis. This is where cursors come into play.

What is a database cursor

A database cursor is an object that enables traversal over the rows of a result set. It allows you to process individual row returned by a query.

SQL Server cursor life cycle

These are steps for using a cursor:

First, declare a cursor.

DECLARE cursor_name CURSOR
    FOR select_statement;
Code language: SQL (Structured Query Language) (sql)

To declare a cursor, you specify its name after the DECLARE keyword with the CURSOR data type and provide a SELECT statement that defines the result set for the cursor.

Next, open and populate the cursor by executing the SELECT statement:

OPEN cursor_name;
Code language: SQL (Structured Query Language) (sql)

Then, fetch a row from the cursor into one or more variables:

FETCH NEXT FROM cursor INTO variable_list;
Code language: SQL (Structured Query Language) (sql)

SQL Server provides the @@FETCHSTATUS function that returns the status of the last cursor FETCH statement executed against the cursor; If @@FETCHSTATUS returns 0, meaning the FETCH statement was successful. You can use the WHILE statement to fetch all rows from the cursor as shown in the following code:

WHILE @@FETCH_STATUS = 0  
    BEGIN
        FETCH NEXT FROM cursor_name;  
    END;
Code language: SQL (Structured Query Language) (sql)

After that, close the cursor:

CLOSE cursor_name;
Code language: SQL (Structured Query Language) (sql)

Finally, deallocate the cursor:

DEALLOCATE cursor_name;
Code language: SQL (Structured Query Language) (sql)

SQL Server cursor example

We’ll use the prodution.products table from the sample database to show you how to use a cursor:

First, declare two variables to hold product name and list price, and a cursor to hold the result of a query that retrieves product name and list price from the production.products table:

DECLARE 
    @product_name VARCHAR(MAX), 
    @list_price   DECIMAL;

DECLARE cursor_product CURSOR
FOR SELECT 
        product_name, 
        list_price
    FROM 
        production.products;
Code language: SQL (Structured Query Language) (sql)

Next, open the cursor:

OPEN cursor_product;
Code language: SQL (Structured Query Language) (sql)

Then, fetch each row from the cursor and print out the product name and list price:

FETCH NEXT FROM cursor_product INTO 
    @product_name, 
    @list_price;

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @product_name + CAST(@list_price AS varchar);
        FETCH NEXT FROM cursor_product INTO 
            @product_name, 
            @list_price;
    END;
Code language: SQL (Structured Query Language) (sql)

After that, close the cursor:

CLOSE cursor_product;
Code language: SQL (Structured Query Language) (sql)

Finally, deallocate the cursor to release it.

DEALLOCATE cursor_product;
Code language: SQL (Structured Query Language) (sql)

The following code snippets put everything together:

DECLARE 
    @product_name VARCHAR(MAX), 
    @list_price   DECIMAL;

DECLARE cursor_product CURSOR
FOR SELECT 
        product_name, 
        list_price
    FROM 
        production.products;

OPEN cursor_product;

FETCH NEXT FROM cursor_product INTO 
    @product_name, 
    @list_price;

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @product_name + CAST(@list_price AS varchar);
        FETCH NEXT FROM cursor_product INTO 
            @product_name, 
            @list_price;
    END;

CLOSE cursor_product;

DEALLOCATE cursor_product;
Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

In practice, you will rarely use the cursor to process a result set in a row-by-row manner.

In this tutorial, you have learned how to use the SQL Server cursor to process a result set, each row at a time.