Guruji Point - Code You Want To Write

  • Home
  • Asp.Net
  • WCF
  • SQL
  • Type Script
  • AngularJS
  • Tech News
  • Blog
  • Earn Online

Friday, 14 April 2017

Cursor In SQL Server With Simple Example

 Cursor in SQL server, What is Cursor, while loop and cursor differnece, why do we need cursor     No comments   


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.



  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to XShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Facebook Updates

Guruji Point

Categories

Comman table Expression in SQL Dynamic Grid Row IQueryable in c# Learn AngularJS Scope_Identity() Use of indexes comma seperated string in sql row_number() and dense_rank() salary table structure. while loop in sql why do we need cursor why tuple used

About Us

This blog is about the Programming ,Tech News and some intresting facts related Contents. Here you can find fundamental and expert level topic of programming languages very practically related to C# , Asp.Net ,Sql-Server, JavaScript, Jquery, WebServices And also Web-Api, WCF ,WPF, Angular Js.

Contact Us

Email Us - gurujipoints@gmail.com
Contact- 8077657477

Reach Us

Copyright © Guruji Point - Code You Want To Write