Cursor

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.

12	DECLARE cursor_name CURSOR    FOR select_statement;

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 popular the cursor by executing the SELECT statement:

1	OPEN cursor_name;

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

1	FETCH NEXT FROM cursor INTO variable_list;

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:

1234	WHILE @@FETCH_STATUS = 0      BEGIN        FETCH NEXT FROM cursor_name;      END;

After that, close the cursor:

1	CLOSE cursor_name;

Finally, deallocate the cursor:

1	DEALLOCATE cursor_name;

SQL Server cursor example

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

12345678910

DECLARE     @product_name VARCHAR(MAX),     @list_price   DECIMAL; DECLARE cursor_product CURSORFOR SELECT         product_name,         list_price    FROM         production.products;

Next, open the cursor:

1	OPEN cursor_product;

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

1234567891011

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;

After that, close the cursor.

1	CLOSE cursor_product;

Finally, deallocate the cursor to release it.

1	DEALLOCATE cursor_product;

The following code snippets put everything together:

12345678910111213141516171819202122232425262728

DECLARE     @product_name VARCHAR(MAX),     @list_price   DECIMAL; DECLARE cursor_product CURSORFOR 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;

Here is the partial output: