Aspdotnet-Suresh

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

What is Cursor in SQL Server with Example

Apr 1, 2015
Introduction:

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.
Description:
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


DECLARE cursorname CURSOR
FOR selectstatement -- like SELECT OrderID,CustomerID FROM Orders
OPEN cursor
FETCH tablerow FROM cursor
Process Fetched Tablerow
CLOSE cursor
DEALLOCATE 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

Example

Write cursor script like as shown below and run it.


DECLARE ex_cursor CURSOR
FOR SELECT OrderID,CustomerID FROM Orders
DECLARE @oid INT
DECLARE @cname NVARCHAR(50)
OPEN ex_cursor
FETCH NEXT FROM ex_cursor INTO @oid,@cname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT  (CAST(@oid AS VARCHAR(5)) + '-' + @cname)
FETCH NEXT FROM ex_cursor INTO @oid,@cname
END
CLOSE ex_cursor
DEALLOCATE ex_cursor
Output:

When we run above query our results like as shown below

Demo

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

Get the latest Asp.net, C#.net, 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

2 comments :

sriram said...

Nice Explanation.....

Anonymous said...

Great :)

Give your Valuable Comments

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

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