Reader Level:
Article

@@Fetch_Status Function in SQL Server 2012

By Deepak Middha on Dec 07, 2012
In this article I described @@Fetch_Status 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

Fetch_status-in-sql-server.jpg

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:

Fetch_status-in-sql-server-2012.jpg

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:


Fetch_status-in-sql-server-2012-.jpg

select
* from #a

Fetch_status-in-sql-server.jpg

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.

Deepak Middha

Deepak Middha is one time Microsoft MVP(Client App Dev) , working as Sr. Software Developer with lots of hands on experience using ASP.NET 2.0/3.5, AJAX, MVC, C#,  SQL Server 2005/2008/2012, WCF, JQuery and Silverl... Read more

COMMENT USING