Basics of CURSOR in SQL Server

Here we will learn about SQL Cursor. Cursor is a database object to retrieve data from a result set one row at a time. Cursor is also used when the data needs to be updated row by row.

Lets understand the life cycle of Cursor.

Declare Cursor

A cursor is declared by defining the SQL select statement that returns a result set. This operation initialize the memory for cursor.

Open Cursor

A Cursor is opened by executing the SQL select statement defined by the cursor. This operation Allocates memory for cursor based on select statement used in cursor declaration

Fetch Data

On this stage a single row is fetched from select statement and assigns value into given variables.

Once the row is fetched into declared variables, you can use your data manipulations or any operations on fetched data.

Loop till last row of select statement

Closing Cursor

Close the Cursor to release fetched rows

Deallocate Cursor

Release memory by deallocating cursor

Understanding life cycle with Syntax

Syntax to Declare a Cursor

 DECLARE cursor_name CURSOR
    FOR select_statement; 

To Declare a Cursor provide the Name of Cursor after DECLARE keyword with CURSOR as data type. For declaration of Cursor, we have to provide select statement which returns one or more columns. Select statement can be passed after FOR keyword.

Syntax to Open Cursor

Opening the cursor will execute select statement which is provided with Declaration. This execution will allocate memory for the Cursor

 OPEN cursor_name; 

Syntax to Fetch Data

Fetch statement provides the many options to retrieve the rows from the cursor. NEXT is the default option. This statement returns a row cells into specified variables

FETCH NEXT FROM cursor INTO variable_list;

Fetch statement reads only a single row into variables. To read all the rows, we have to run while loop with Fetch Statement and get all the rows one by one.

        FETCH NEXT FROM cursor_name;  

If @@FETCHSTATUS returns 0, meaning the FETCH statement was successful.

Syntax to Close Cursor

This statement closes the cursor explicitly

CLOSE cursor_name;

Syntax to Deallocate Cursor

DEALLOCATE statement delete the cursor and release all the resources. Deallocated Cursor can not be reopened.

DEALLOCATE cursor_name 

Cursor Example

In this example we will read the CustomerOrder table. We will read today’s orders and verify the information provided by customer.

    @OrderID int, @CustomerName varchar(Max),@OrderAmount decimal(18,2);
DECLARE cursor_Orders CURSOR
SELECT OrderID,CustomerName,OrderAmount 
FROM CustomerOrders where cast(orderdate as date)= cast(getdate() as date);

After declaration lets read all the rows and do manipulation on every row.

    @OrderID, @CustomerName, @OrderAmount;
-- Here we can do any operations on the fetched row data.
    @OrderID, @CustomerName, @OrderAmount;

Once use of the cursor is completed, close it and deallocate it.

Close cursor_Orders ;
DEALLOCATE cursor_Orders ;

This way we can use CURSOR to read every row and do manipulation on every single row’s data.

Write down your questions in comment section if you have any.