SQL Cursors

Cursor

-   Cursors allow roe by row processing of the results. 

-  Cursor is a database object to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time.

- We use cursor when we need to update records in a database table in singleton fashion means row by row.

Life Cycle of Cursor

1. Declare Cursor

   A cursor is declared by defining the SQL statement that returns a result set.

2. Open

   A Cursor is opened and populated by executing the SQL statement defined by the cursor.

3. Fetch

   When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.

4. Close

   After data manipulation, we should close the cursor explicitly.

5. Deallocate

   Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

Syntax to Declare Cursor

Declare Cursor SQL Command is used to define the cursor with many options that impact the scalability and loading behavior of the cursor. The basic syntax is given below

Syntax to Open Cursor

A Cursor can be opened locally or globally. By default it is opened locally. The basic syntax to open cursor is given below:

Syntax to Fetch Cursor

Fetch statement provides the many options to retrieve the rows from the cursor. NEXT is the default option. The basic syntax to fetch cursor is given below:

Syntax to Close Cursor

Close statement closed the cursor explicitly. The basic syntax to close cursor is given below:

Syntax to Deallocate Cursor

Deallocate statement delete the cursor definition and free all the system resources associated with the cursor. The basic syntax to close cursor is given below:

SQL SERVER – Simple Examples of Cursors

Summary

In this article I try to explain the basic of Cursor in SQL Server with a simple example. I hope after reading this article you will be able to understand cursors in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.