How to Retrieve Row Using Cursor in SQL

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

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

inv111.jpg

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

mcnven.jpg

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

Clipboard12.jpg

A Statement that retrieves next row from cursor

Clipboard14.jpg

A Statement that retrieves first row from cursor

Clipboard17.jpg

A Statement that retrieves last row from cursor

Clipboard19.jpg

A Statement that retrieves previous row from cursor

Clipboard21.jpg

A Statement that retrieves fifth row from first row of cursor

Clipboard23.jpg

A Statement that retrieves third row from current row of cursor

Clipboard25.jpg


© 2019 DotNetHeaven. All rights reserved.