How to Retrieve Row Using Cursor in SQL

Posted in SQL | Cursor on July 26, 2019

Tags: Cursor in SQL Retrieve next row from cursor, Retrieve first row from cursor, Retrieve last row from cursor, Retrieve previous row from cursor

In this article I am going to explain how to retrieve row using cursor in SQL.

2974

Introduction

A cursor is a database object that points to a result set. We use cursor to fetch any specific row from result set. Most of time cursor is used by application programmer.

Implementation of cursor in SQL

We can implement cursor through standard database APIs.
We can implement cursor through Transact-SQL

SQL Statement used in cursor

DECLARE : It is used to define a new cursor.
OPEN : It is used to open a cursor
FETCH : It is used to retrieve a row from a cursor.
CLOSE : It is used to close a cursor.
DEALLOCATE : It is used to delete a cursor and releases all resources used by cursor.

FETCH statement is used to retrieve the specific row from the cursor.

A Statement that is used to create a mcninvoices table

create table mcninvoices

(

invoiceid int not null identity primary key,

vendorid int not null,

invoiceno varchar(15),

invoicetotal money,

paymenttotal money,

creadittotal money

)

A Statement that is used to insert data in mcninvoices table

insert into 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)

A Statement that show data in mcninvoices table

A Statement that is used to create a mcnvendors table

create table mcnvendors

(

vendorid int,

vendorname varchar(15),

vendorcity varchar(15),

vendorstate varchar(15)

)

A Statement that is used to insert data in mcnvendors table

insert into mcnvendors values (20,'vipendra','noida','up')

insert into mcnvendors values (21,'deepak','lucknow','up')

insert into mcnvendors values (22,'rahul','kanpur','up')

insert into mcnvendors values (23,'malay','delhi','delhi')

insert into mcnvendors values (24,'mayank','noida','up')

A Statement that show data in mcnvendors table

A Statement that is used to create a cursor

declare cur_invinfo cursor
global static
for
      select vendorname,vendorcity,invoicetotal
      from mcnvendors join mcninvoices
      on mcnvendors.vendorid=mcninvoices.vendorid

A Statement that is used to open a cursor

open cur_invinfo

A Statement that retrieves next row from cursor