How to use @@FETCH_STATUS Function with Cursor in SQL

In this article I am going to explain how to use @@FETCH_STATUS Function With Cursor in SQL.
  • 5168

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_STATUS system function

@@FETCH_STATUS system function is used to find the most recent FETCH statement.  You can use this function with while loop. @@FETCH_STATUS system function return 0 or 1. When it returns 0 it means the FETCH is successful and it is equal to zero. When it returns 1 it means the FETCH is unsuccessful and it is equal to one.

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


Statement that use
@@FETCH_STATUS system function

Clipboard27.jpg

© 2019 DotNetHeaven. All rights reserved.