ARTICLE

How to Fetch Record Using Cursor in SQL Server 2012

Posted by Vipendra Verma Articles | SQL Server August 24, 2012
In this article I am explaining how to fetch records using a cursor in SQL Server 2012.
Reader Level:

Introduction

In this article I am explaining how to  fetch records using a cursor in SQL Server 2012. A cursor in SQL serever 2012 is a database object that points to a result set. We use a cursor in SQL Server 2012 to retrieve a specific row or data from a result set.

There are two ways to implement a cursor in SQL Server 2012:

  • Standard Database APIs : We can implement a cursor through standard database APIs.
  • Through Transact-SQL : We can implement a cursor through Transact-SQL

Implementation of Cursor in SQL Server

To create a cursor we use various clauses. A DECLARE clause is used to define a new cursor, an OPEN clause is used to open the cursor and a FETCH clause is used to retrieve data from a result set. CLOSE is used to close a cursor. A DEALLOCATE clause is used to delete a cursor.

Types of cursors in SQL Server 2012:

  • Dynamic cursor
  • Keyset cursor
  • Static cursor
  • Forward-only cursor
  • Forward-only KEYSET cursor
  • Forward-only stataic cursor
  • Fast_Forward cursor

Statement that creates a table

createtable mcninvoices
(

invoiceid
int notnull identityprimary key,
vendorid
int notnull,
invoiceno
varchar(15),
invoicetotal
money,
paymenttotal
money,
creadittotal
money

)

 Statement that inserts data into a table

insertinto mcninvoices values (20,'e001',100,100,0.00)
insert
into mcninvoices values (21,'e002',200,200,0.00)
insert
into mcninvoices values (22,'e003',500,0.00,100)
insert
into mcninvoices values (23,'e004',1000,100,100)
insert
into mcninvoices values (24,'e005',1200,200,500)
insert
into mcninvoices values (20,'e007',150,100,0.00)
insert
into mcninvoices values (21,'e008',800,200,0.00)
insert
into mcninvoices values (22,'e009',900,0.00,100)
insert
into mcninvoices values (23,'e010',6000,100,100)
insert
into mcninvoices values (24,'e011',8200,200,500)

Statement that shows all data of mcninvoicetable

t (1).jpg

Statement that creates the mcnvendors table in SQL Server 2012

createtable mcnvendors

 (

 vendoridint,

 vendornamevarchar(15),

 vendorcityvarchar(15),

 vendorstatevarchar(15)

 )

Statements that insert data into the mcnvendors table in SQL Server 2012

insertinto mcnvendors values (20,'vipendra','noida','up')

insertinto mcnvendors values (21,'deepak','lucknow','up')

insertinto mcnvendors values (22,'rahul','kanpur','up')

insertinto mcnvendors values (23,'malay','delhi','delhi')

insertinto mcnvendors values (24,'mayank','noida','up')

A statement that is used to fetch data from the mcnvendors table in SQL Server 2012

t (2).jpg

A statement that is used to create a Cursor in SQL Server 2012

Here I am going to create a invoiceinfo_cr cursor which is global and static, which means it is accessible by all users and it is scrollable but not sensitive to a database. This cursor creates a resultset which retrieves data from two tables using join.

DECLAREinvoiceinfo_cr CURSOR

GLOBALSTATIC

FOR

     SELECT vendorname,vendorcity,invoicetotal

     FROM mcnvendorsjoin mcninvoices

     ON mcnvendors.vendorid=mcninvoices.vendorid

 

Statement that is used to open a cursor in SQL Server 2012


Clipboard10.jpg


Statement that retrieves the next row from a cursor in SQL Server 2012


Clipboard12.jpg

 

Another statement that retrieves the next row from a cursor in SQL Server 2012


Clipboard14.jpg


Clipboard16.jpg


Clipboard18.jpg


 

Statement that retrieves a previous  row from a cursor in SQL Server 2012


Clipboard20.jpg


Clipboard22.jpg


Clipboard24.jpg

 

Statement that retrieves the first row from a cursor in SQL Server 2012


Clipboard26.jpg

 

Statement that retrieves the last row from a cursor in SQL Server 2012

 

Clipboard28.jpg


Statement that retrieves the second row after the first row of a cursor in SQL Server 2012


Clipboard30.jpg

 

Statement that retrieves the second row after the current row from cursor in SQL Server 2012


Clipboard32.jpg

 

Statement that retrieves the second row before the current row from a cursor in SQL Server 2012


Clipboard34.jpg

COMMENT USING