SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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)

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

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

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
 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
    Set @addamt=(20*@amount)/100
    Update customer set amount=amount+@addamt where customerid=@custid
    Insert into  customerTransaction(custid,Transactionamt,mode,trandate )
   Fetch Next From cur_cust Into @custid,@amount
CLOSE cur_cust

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. 

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.


No comments.

Leave a Comment

Please register or log in to leave a comment.