ARTICLE

How to Use @@FETCH_STATUS Function in SQL Server 2012

Posted by Vipendra Verma Articles | SQL Server August 27, 2012
In this article I am going to explain how to use the @@FETCH_STATUS function in SQL Server 2012.
Reader Level:

Introduction

In this article I am going to explain how to use the @@FETCH_STATUS function in SQL Server 2012. We can use it with a curosr.

@@FETCH_STATUS Function

To find the most recent FETCH statement in SQL Server 2012 we use the @@FETCH_STATUS system function.  We can use the @@FETCH_STATUS system function with a while loop in SQL Server 2012. The @@FETCH_STATUS system function returns three values in SQL Server 2012 which are explained below.

When @@FETCH_STATUS system function returns 0 the FETCH is successful and it is equal to zero.

When @@FETCH_STATUS system function returns -1 the FETCH was unsuccessful.

When @@FETCH_STATUS system function returns -2 the FETCH was unsuccessful because the row was deleted.

There are two ways to implement a cursor in SQL Server 2012; they are:

  • Standard Database APIs : We can implement a cursor using standard database APIs.
  • Through Transact-SQL : We can implement a cursor using Transact-SQL.

The types of cursors in SQL Server 2012 are:

  • Dynamic cursor
  • Keyset cursor
  • Static cursor
  • Forward-only cursor
  • Forward-only KEYSET cursor
  • Forward-only static cursor
  • Fast_Forward cursor

Statement that creates a table:

createtable mcninvoices
(

invoiceid
int notnull identityprimary key,
vendorid
int notnull,
invoiceno
varchar(15),
invoicetotal
money,
paymenttotal
money,
creadittotal
money

)

 Statement that inserts data into the table:

insertinto 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)
insert
into mcninvoices values (20,'e007',150,100,0.00)
insert
into mcninvoices values (21,'e008',800,200,0.00)
insert
into mcninvoices values (22,'e009',900,0.00,100)
insert
into mcninvoices values (23,'e010',6000,100,100)
insert
into mcninvoices values (24,'e011',8200,200,500)

Statement that shows all data of mcninvoicetable:

SelectStatement.jpg

Statement that creates the mcnvendors table in SQL Server 2012:

createtable mcnvendors

 (

 vendoridint,

 vendornamevarchar(15),

 vendorcityvarchar(15),

 vendorstatevarchar(15)

 )

Statements that insert data in  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:

FetchData.jpg

A Statement that is used to create a Cursor in SQL Server 2012:

Here I am going to create a cur_invinf cursor which is global and static. That means it is accessible by all users and it is scrollable but not sensitive to database. This cursor creates a result set which retrieves data from two tables using join.

DECLAREcur_invinf 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:


OpenCursor.jpg

 

Statement that uses the @@FETCH_STATUS  function with a cursor in SQL Server 2012:


UseCursor.jpg

COMMENT USING