@@Fetch_Status Function in SQL Server 2012

Introduction

In this article I describe the @@fetch_status function in SQL Server that is used with a Cursor. I asume you have a basic knoweldge of Cursors; for more help you can visit the following links:

Cursor in SQL Server Part 1
Cursor in SQL Server Part 2

@@Fetch_Status:

It returns the last status of the fetch operation of the cursor which is currently opened and in use. We can say that the @@Fetch_Status function returns the status of a recent fetch operation of a cursor.

It is used with a while loop. The @@fetch_status returns 0,-1 or -2. When it returns 0 the fetch operation was successful. When it returns -1 it indicates that the FETCH statement failed and when it returns -2 it shows that the Row fetched is missing.

Examples of @@Fetch_status Function:

First of all we create a table and a cursor on that table.

Creation of Table:

create table stu(stuId int, stuName varchar(10))

Insertion of Data:

insert into stu
select 1,'Deepak'union all
select 2,'daljeet'union all
select 3,'pankaj'union all
select 4,'govind 'union all
select 5,'Ravi'

Output:

select * from stu



Creation of a scroll Cursor:

declare stuCursor cursor
scroll for
select * from stu

Example 1:

We can fetch all the rows from a table using the @@Fetch_status function and a Cursor. See:

fetch first from stuCursor 
while @@FETCH_STATUS=0
begin
fetch next from stuCursor
end

Output:



Example 2:

In this example we can fetch all the records from the table stu and insert data in a temprary table named #a:

declare @id int
Declare @name varchar(10)
fetch first from stuCursor into @id,@name
insert into #a values(@id,@name)
while @@FETCH_STATUS=0
begin
fetch next from stuCursor into @id,@name
insert into #a values(@id,@name)
end

Output:



select * from #a



Summary:

In this article I described @@Fetch_Status in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.