SQL Server  

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
  1. create table mcninvoices  
  2. (  
  3. invoiceid int not null identity primary key,  
  4. vendorid int not null,  
  5. invoiceno varchar(15),  
  6. invoicetotal money,  
  7. paymenttotal money,  
  8. creadittotal money  
  9. )  
Statement that inserts data into the table
  1. insert into mcninvoices values (20,'e001',100,100,0.00)  
  2. insert into mcninvoices values (21,'e002',200,200,0.00)  
  3. insert into mcninvoices values (22,'e003',500,0.00,100)  
  4. insert into mcninvoices values (23,'e004',1000,100,100)  
  5. insert into mcninvoices values (24,'e005',1200,200,500)  
  6. insert into mcninvoices values (20,'e007',150,100,0.00)  
  7. insert into mcninvoices values (21,'e008',800,200,0.00)  
  8. insert into mcninvoices values (22,'e009',900,0.00,100)  
  9. insert into mcninvoices values (23,'e010',6000,100,100)  
  10. 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
  1. create table mcnvendors  
  2. (  
  3.     vendorid int,  
  4.     vendorname varchar(15),  
  5.     vendorcity varchar(15),  
  6.     vendorstate varchar(15)  
  7. )  
Statements that insert data in  the mcnvendors table in SQL Server 2012
  1. insert into mcnvendors values (20,'vipendra','noida','up')  
  2. insert into mcnvendors values (21,'deepak','lucknow','up')  
  3. insert into mcnvendors values (22,'rahul','kanpur','up')  
  4. insert into mcnvendors values (23,'malay','delhi','delhi')  
  5. insert into 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.
  1. DECLARE cur_invinf CURSOR  
  2. GLOBAL STATIC   
  3. FOR  
  4.      SELECT vendorname,vendorcity,invoicetotal  
  5.      FROM mcnvendorsjoin mcninvoices  
  6.      ON mcnvendors.vendorid=mcninvoices.vendorid  
Statement that is used to open a cursor in SQL Server 2012
 
Open Cursor in SQL Server 
 
Statement that uses the @@FETCH_STATUS function with a cursor in SQL Server 2012
 
@@FETCH_STATUS function in SQL Server 

Membership not found