Cursor in SQL server

Sometimes our application required a database object which allows manipulation of data from a set of rows on row by row basic which means single row at a time. Cursor is the database object which solves this problem. With the use of cursor, we can fetch a sets of rows based on some conditions and manipulate the data of a single row at a time.

Section of the cursor:-

   The Cursor is consists of the following sections:-

  1. Declaration of Cursor:- This section is used to declare the cursor object
  2. Sql Statement for fetching the records:- This section is used to define the SQL query used for fetching the record set
  3. Open Cursor:- This section is used to Open the Cursor. Also this statement allocates the memory to the cursor to hold the record set return by the execution of the Sql Statement.
  4. Fetch statement:- This section is used to fetch a single row from the record set get by the execution of the Sql Query and assigning the values to the respective variable.
  5. @@Fetch_status:- This is the System variable and it is used to know that whether the Fetch statement is returning rows or not. This system variable returns the value of 0 if the Fetch statement is successful, -1 if the Fetch statement is failed and -2 if the row fetched is missing. While loop is used compare the value of the @@Fetch_status with the 0.
  6. Begin……End:- This section is used to write the Sql code used inside the Cursor to manipulates the data of the rows fetch by the Fetch Statement
  7. Close Cursor:- This statement is used to close the cursor.
  8. Deallocate Cursor:- This section is used to deallocated the memory used by the cursor.

Syntax for defining the Cursor:-

  Declare @Cursor_name cursor For
    Sql query                                                        // Select statement to Fetch rows
 Open @Cursor_name
  Fetch next from @Cursor_name  into {@Variable1,@variable2 etc)
  While (@@fetch_status=0)
   Begin

      // Sql Block  sql statements to process the data of the row return by the cursor.

 Fetch next from @Cursor_name  into {@Variable1,@variable2 etc)
 End
 Close @Cursor_name
 Deallocate @Cursor_name

Example:-
Suppose we have two tables named customer and customerTransaction whose structure is given below:-

Create Table customer(customerid int identity(1,1) primary key,Custnumber nvarchar(100),custFname nvarchar(100), CustEnamn nvarchar(100),email nvarchar(100),Amount int, regdate datetime)
Create Table customerTransaction(Transactionid int identity(1,1) primary key,custid int, Transactionamt int,mode nvarchar, trandate datetime)
Customer table contains the customer information and the table customerTransaction contains the information about the transactions (credit or debit) done by the customer. Whenever any transaction occur, it entry should also be inserted into the table customerTransaction.
Suppose we have an requirement that all the customer amount should be increased by 20% and this transaction entry should be made into the table customerTransaction, then we can use the cursor for this purpose. Cursor is used mostly within the stored procedures or the SQL function. In our example we used the cursor inside the stored procedure named Updatecustamount whose Sql Script is given below:-

Create Procedure Updatecustamount
  As
Begin
 Declare @custid as Int
 Declare @amount as Int
 Declare @addamt as Int
 Declare cur_cust Cursor For
   Select customerid,amount
   From customer

 OPEN Cur_cust

  FETCH Next From cur_cust Into @custid,@amount
 While(@@fetch_status=0)
  Begin
    Set @addamt=(20*@amount)/100
    Update customer set amount=amount+@addamt where customerid=@custid
 
    Insert into  customerTransaction(custid,Transactionamt,mode,trandate )
                   Values(@custid,@addamt,’c’,Getdate())
   Fetch Next From cur_cust Into @custid,@amount
  End
CLOSE cur_cust
DEALLOCATE cur_cust
End

Summary:-
We can say that cursor is a very useful database object if we needs to manipulates the rows on a row by row basic. But there are two camps, one which oppose the use of cursor since it sometimes slow down the performance by occupying a lot of memory to hold the rows return by the query and the other camp which advocates the use of cursor since it gives the readability and simplicity to our code. We can replace the cursor with the help of the While loop or sometimes with the help of the Select case statements.One can also used the cursor if the need is to execute the Sql statements in a serialized manner.Also we can use the cursor for doing many Administrative tasks. 

DMCA.com

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Most Imp SQL Concepts, SQL Cursor, SQL Server and tagged , , . Bookmark the permalink.

33 Responses to Cursor in SQL server

  1. Prashant Avhad says:

    Very good article for beginners

  2. Vivek Johari says:

    Thanks Prashant for your response.:-)

  3. Anonymous says:

    Thanks Helpfull for me.

  4. Pingback: event planners in durban

  5. Pingback: agencia de viajes en managua

  6. Pingback: Best Real Estate Agent Tallahassee

  7. Pingback: concrete polishing

  8. Pingback: have a peek at this web-site

  9. Pingback: website

  10. Pingback: read here

  11. Pingback: buy youtube subscribers

  12. Pingback: Hotel Copenhagen

  13. Pingback: The Bull Market in Gold Silver Mining Stocks has started Stock Market Economy

  14. Pingback: บาคาร่า

  15. Pingback: Totojitu

  16. Pingback: GCLUB

  17. Pingback: www.digitalmarketingforbusinessbook.com

  18. Pingback: 바카라사이트

  19. Pingback: ballast cleaning

  20. Pingback: How to watch Rugby World Cup 2019 live stream online instantly

  21. Pingback: ถ่ายทอดสดเสือมังกรออนไลน์

  22. Pingback: raja qq

  23. Pingback: road cases decals

  24. Pingback: http://dinozaurybiznesu.pl/pamiatka-pierwszej-komunii-swietej-535.php

  25. Pingback: drug and alcohol rehab

  26. Pingback: https://internetnews.net.pl/wybierz-odpowiednie-spinki-koszuli-lub-garnituru/

  27. Pingback: Cheetah Miner F5i-60T

  28. Pingback: social impact marketing

  29. Pingback: emergency same day dentures repairs indianapolis

  30. Pingback: Replica where to buy rolex

  31. Pingback: Tochka Market

  32. Pingback: m88a

Leave a Reply