How to Use @@FETCH_STATUS Function in SQL Server 2012

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 cursor.

 

@@FETCH_STATUS Function in SQL Server

 

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

create table mcninvoices
(
invoiceid int not null identity primary key,
vendorid int not null,
invoiceno varchar(15),
invoicetotal money,
paymenttotal money,
creadittotal money
)

Statement that inserts data into the 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)
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

 

 

 

Statement that creates the mcnvendors table in SQL Server 2012

create table mcnvendors
(
    vendorid int,
    vendorname varchar(15),
    vendorcity varchar(15),
    vendorstate varchar(15)
)

Statements that insert data in  the mcnvendors table in SQL Server 2012

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 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 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.

DECLARE cur_invinf CURSOR
GLOBAL STATIC
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 uses the @@FETCH_STATUS function with a cursor in SQL Server 2012