Question

Define SQL Server Cursor?

Answer

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 @Cursorname cursor For
    Sql query                                                        // Select statement to Fetch rows
 Open @Cursorname
  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 @Cursorname  into {@Variable1,@variable2 etc)
 End
 Close @Cursorname
 Deallocate @Cursorname

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 curcust Cursor For

   Select customerid,amount From customer
 OPEN Curcust
  FETCH Next From curcust 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 )

Read More..   [32134 clicks]

Published under: SQL Server Interview Questions ·  ·  ·  ·