aspdotnet-suresh offers articles and tutorials,csharp dot net, articles and tutorials,VB.NET Articles,Gridview articles,code examples of 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

What is Cursor in SQL Server with Example

Apr 1, 2015

Here I will explain what is cursor in
sql server with example or simple cursor example in sql server and how to declare and use cursor in sql server. Cursor in sql server is used to retrieve set of data from table, loop through each record row by row, and modify the values based on requirements.
In previous articles I explained While loop example in sql server, nested while loop example in sql server, get only month and year from date in sql server, substring function in SQL server and many articles relating to SQL server. Now I will explain cursor in sql server with example.

To use cursor in sql server that syntax will be like as shown below

Syntax to Create Cursor

FOR selectstatement -- like SELECT OrderID,CustomerID FROM Orders
OPEN cursor
FETCH tablerow FROM cursor
Process Fetched Tablerow
CLOSE cursor
If you observe above syntax we have different steps in cursor

Step 1: Declare Cursor

First we need to declare cursor name that will be used in cursor execution

Step 2: Select Statement

Select statement is used to get data from table to process with cursor

Step 3: Open Cursor

This statement is used to open the cursor to process select statement result set

Step 4: Fetch Rows

Once cursor is opened, rows can be fetched from the cursor one by one

Step 5: Close Cursor

Once our process finished we need to close the cursor

Step 6: Deallocate Cursor

We need to deallocate cursor to delete cursor definition and release resources associated with the cursor

We will check this with example for that first create one table UserDetails in your database and insert some 100 or 200 records or install northwind database in your server and use orders table it contains more than 500 records


Write cursor script like as shown below and run it.

FOR SELECT OrderID,CustomerID FROM Orders
OPEN ex_cursor
FETCH NEXT FROM ex_cursor INTO @oid,@cname
PRINT  (CAST(@oid AS VARCHAR(5)) + '-' + @cname)
FETCH NEXT FROM ex_cursor INTO @oid,@cname
CLOSE ex_cursor
DEALLOCATE ex_cursor

When we run above query our results like as shown below


If you enjoyed this post, please support the blog below. It's FREE!

Get the latest,, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email


sriram said...

Nice Explanation.....

Anonymous said...

Great :)

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.