About Cursor in Sql Server - Database

random

About Cursor in Sql Server - Database

All about Sql Server: example of Cursor in sql server

What is a Cursor in SQL Server?

A Cursor is a SQL Object, or we can say like a virtual table that retrieves data from the table one row at a time. We use cursors when we need to update records in a database table in singleton fashion in other words row by row.

Life Cycle of Cursor:

Declare Cursor - > Open -> Fetch - > Close -> Deallocate 

Before using a cursor, you first must declare the cursor. Once a cursor has been declared, you can open it and fetch from it. You can fetch row by row and make multiple operations on the currently active row in the cursor. When you have finished working with a cursor, you should close the cursor and Deallocate it to release SQL Server resources.

Type of cursors:
- Forward Only Cursor,
- Scroll Cursor,
- Static Cursor,
- Dynamic Cursor,
- Keyset Driven Cursor

First of all we create a table for applying our Cursor.

create table emp(emp_id int,em_name varchar(10))

After the creation we insert data as follows:

insert into emp(emp_id,em_name) VALUES(1,'d')
insert into emp(emp_id,em_name) VALUES(2,'deepak')
insert into emp(emp_id,em_name) VALUES(3,'gaurav')
insert into emp(emp_id,em_name) VALUES(4,'mahi')
insert into emp(emp_id,em_name) VALUES(5,'gill')
insert into emp(emp_id,em_name) VALUES(6,'singh')

Output:
cursor in sql,about cursor, sql server, database

Forward only and Scroll Cursors are important So I explain them in detail. 

Forward Only cursor
This type of Cursor fetches the next record only. In this type of cursor we can fetch only next record we cant fetch first, last and a specific record.

Declaration of Forward Only cursor:
declare curscr  cursor
for
select * from emp

Opening the forward-only Cursor
open curscr

Fetching data from a Forward Only cursor
fetch next from curscr

If we execute this query two times than we get the following output:



Closing the Forward Only Cursor:
close curscr

Deallocate/Deleting the Forward Only Cursor:
deallocate curscr

Scroll Cursor:
We can fetch any record as first, last, prior and specific record from the table.

Declaration of Scroll cursor:
declare scroll_cursor cursor
scroll for
select * from emp

Opening the Scroll Cursor:
open scroll_cursor

Fetching data from Scroll cursor:

- fetch first from scroll_cursor (To fetching the first data)

- fetch last from scroll_cursor (To fetching the last data)

- fetch prior from scroll_cursor (To fetching the previous data)

- fetch absolute 4 from scroll_cursor (To fetching the absolute data)

- fetch relative 2 from scroll_cursor (To fetching the relative data)

Closing the Scroll Cursor:
close scroll_cursor

Deallocate/Deleting the Scroll Cursor :
deallocate scroll_cursor

Syntax to Declare Cursor:

Declare Cursor SQL Command is used to define the cursor with many options that impact the scalability and loading behavior of the cursor. The basic syntax is given below:

 DECLARE cursor_name CURSOR
 [LOCAL | GLOBAL] --define cursor scope
 [FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)
 [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor
 [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks
 FOR select_statement --define SQL Select statement
 FOR UPDATE [col1,col2,...coln] --define columns that need to be updated 

Example 1

SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
DECLARE cur_emp CURSOR
STATIC FOR 
SELECT EmpID,EmpName,Salary from Employee
OPEN cur_emp
IF @@CURSOR_ROWS > 0
BEGIN 
FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
WHILE @@Fetch_status = 0
BEGIN
PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF



Example 2:

CREATE PROCEDURE Usp_cursor_test

AS
  BEGIN
      –Declaring the  variables needed for cursor to store data
      DECLARE @Name VARCHAR(50)
      DECLARE @EmptypeID INT
      –Declaring the Cursor cur_print For name and Emptypeid in the Employeedetails table 
      DECLARE cur_print CURSOR FOR
        SELECT name,
               emptypeid
        FROM   employee.employeedetails
      –After declaring we have to open the cursor 
      OPEN cur_print
      –retreives the First row from cursor and storing it into the variables. 
      FETCH NEXT FROM cur_print INTO @Name, @EmptypeID
      – @@FETCH_STATUS returns the status of the last cursor FETCH statement issued against  
      – any cursor currently opened by the connection. 
      – @@FETCH_STATUS = 0 means The FETCH statement was successful. 
      – @FETCH_STATUS = -1 The FETCH statement failed or the row was beyond the result set. 
      – @@FETCH_STATUS = -2 The row fetched is missing. 
      WHILE @@FETCH_STATUS = 0
        BEGIN
            –Operations need to be done,Here just printing the variables 
            PRINT @Name
            PRINT @EmptypeID
            –retreives the NExt row from cursor and storing it into the variables. 
            FETCH NEXT FROM cur_print INTO @Name, @EmptypeID
        END
      –Closing the cursor 
      CLOSE cur_print
      – removes the cursor reference and relase cursor from memory 
      – very Important 
      DEALLOCATE cur_print
  END

Note:
  • Once cursor is opened we have to close the cursor.
  • After the usage cursor should be deallocated from the memory.
  • I will not recommend the usage of cursors in all scenarios because it affects performance, since for each result it will have a  network round trip which will cause a major performance issue in large data sets. You can make use of case statement instead of cursors for some scenarios.

About Cursor in Sql Server - Database Reviewed by Ravi Kumar on 2:43 PM Rating: 5

1 comment:

  1. Thanks sir,
    this is very needfull for me.




    REGARDS:kantiSwaroop

    ReplyDelete

All Rights Reserved by Etechpulse © 2012 - 2017

Contact Form

Name

Email *

Message *

Powered by Blogger.