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

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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