How to Fetch Record Using Cursor in SQL Server 2012

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
(
invoiceidint notnull identityprimary key,
vendoridint notnull,
invoicenovarchar(15),
invoicetotalmoney,
paymenttotalmoney,
creadittotalmoney
)

Statement that inserts data into a table

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

Statement that shows all data of mcninvoicetable

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

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