Development SQL Server Stored Procedures


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:

SQL Server Cursor

First, declare a cursor.

12DECLARE 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:

1OPEN cursor_name;

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

1FETCH 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:

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

After that, close the cursor:

1CLOSE cursor_name;

Finally, deallocate the cursor:

1DEALLOCATE 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:

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

Next, open the cursor:

1OPEN cursor_product;

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

1234567891011FETCH 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.

1CLOSE cursor_product;

Finally, deallocate the cursor to release it.

1DEALLOCATE cursor_product;

The following code snippets put everything together:

12345678910111213141516171819202122232425262728DECLARE     @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:

SQL Server Cursor Example

1 thought on “Cursor”

  1. Great work! This is the type of info that should be shared around the internet.
    Shame on the search engines for no longer positioning this submit higher! Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *