Cursor In SQL Server With Simple Example

Introduction

In this post we will learn what is Cursor and how to use this in SQL server. Why do we need cursor in sql and where to use the cursor.

Previous Updates
In previous articles we have learnt If Else and Case statement  , While Loop in SQL. What is Pivot table in SQl. Difference between Scope_Identity(), @@Identity and Ident_Current . Stuff and Replace in SQl. Temp table and table variable difference and when to use. Sequence in sql server with example.Group By in SQL Server and its use.

What is Cursor
Cursor is a collection of rows having a  pointer which points to the current row in the execution. It allows you to fetch a set of data and loop through this data one by one just like a while loop.  You can modify the data between the execution in loop. By using cursor pointer you point out which row is currently running and you can apply your condition based on that pointer value and modify the record.
Because Cursor is a object with in the database,  you have to face little overhead while creating and destroying the Cursor.

Cursor Syntax

DECLARE @ID varchar(50), @Name varchar(50) DECLARE YourcursorName CURSOR -- Declare cursor LOCAL SCROLL STATIC FOR Select ID, Name FROM YourTable OPEN YourcursorName -- open the cursor   FETCH NEXT FROM YourcursorName    INTO @ID, @Name    PRINT @ID + ' ' + @Name  WHILE @@FETCH_STATUS = 0  BEGIN     FETCH NEXT FROM YourcursorName      INTO @ID, @Name    PRINT @ID + ' ' + @Name  END   CLOSE YourcursorName -- close the cursor   DEALLOCATE YourcursorName -- Deallocate the cursor

In the above syntax you have seen many keywords to write cursor. Here is the explanation all of these 

DECLARE - For to declare a variable.

SELECT - Select or get the values from table

DECLARE CURSOR - Declare or create the cursor.

OPEN - Open the cursor for processing the data.

FETCH NEXT - Assigning the values from the cursor to the variable.

WHILE - Simple looping condition for begin and processing the data 

@@FETCH_STATUS - Loop until records being fetched.

BEGIN/END - Start and end of the loop code block.

CLOSE - Release the current data and associates lock, but permits the cursor to be reopened.

DEALLOCATE - Destroy the cursor at the end.

Practice 
Here i fetch all the data from my TestTable and print row by row by using Cursor and after completion of execution cloase the cursor and deallocate to.  
My Test Table data look like this :- 

DECLARE @ID varchar(50), @Name varchar(50) DECLARE TestCursor CURSOR -- Declare cursor LOCAL SCROLL STATIC FOR Select ID, Name FROM TestTable OPEN TestCursor -- open the cursor   FETCH NEXT FROM TestCursor    INTO @ID, @Name    PRINT'Cursor inserted Values    '+  @ID + ' ' + @Name  WHILE @@FETCH_STATUS = 0  BEGIN     FETCH NEXT FROM TestCursor      INTO @ID, @Name    PRINT'Cursor inserted Values    '+  @ID + ' ' + @Name  END   CLOSE TestCursor -- close the cursor   DEALLOCATE TestCursor -- Deallocate the cursor

After performing the cursor operation over TestTable here is the cursor  output.