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 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 ( invoiceidint notnull identityprimary key, vendoridint notnull, invoicenovarchar(15), invoicetotalmoney, paymenttotalmoney, creadittotalmoney )

 Statement that inserts data into the table:

insertinto mcninvoices values (20,'e001',100,100,0.00) insertinto mcninvoices values (21,'e002',200,200,0.00) insertinto mcninvoices values (22,'e003',500,0.00,100) insertinto mcninvoices values (23,'e004',1000,100,100) insertinto mcninvoices values (24,'e005',1200,200,500) insertinto mcninvoices values (20,'e007',150,100,0.00) insertinto mcninvoices values (21,'e008',800,200,0.00) insertinto mcninvoices values (22,'e009',900,0.00,100) insertinto mcninvoices values (23,'e010',6000,100,100) insertinto mcninvoices values (24,'e011',8200,200,500)

Statement that shows all data of mcninvoicetable:

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:

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:

 

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