Identify when you have reached the last record on a SQL Cursor

Is there anyway to identify when you have reached the last record in a SQL Cursor. I need to manipulate the data within a cusor, but the first and last records need particular data manipulation carried out on them.

Doing this on the first record is relatively straight forward. I am not aware of and EOF property I can check for cursors as you can do in recordsets, does anyone know of a simple way of doing this?

Nathan
LVL 7
NathanIrwinMining AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lara FEACommented:
this is Fetch syntax.
FETCH
          [ [ NEXT | PRIOR | FIRST | LAST
                    | ABSOLUTE { n | @nvar }
                    | RELATIVE { n | @nvar }
               ]
               FROM
          ]
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }
[ INTO @variable_name [ ,...n ] ]
.....
LAST
Returns the last row in the cursor and makes it the current row

http://msdn.microsoft.com/en-us/library/ms180152.aspx
cyberkiwiCommented:
You could try @@CURSOR_ROWS
http://msdn.microsoft.com/en-us/library/ms176044.aspx

Or declare the cursor as STATIC, e.g.

declare c cursor static for ....

And go forward to test
 fetch next from c into @var
 if @@fetch_status = -1
    -- we are on last record
 else
    -- we are not
 fetch prior from c into @var  -- go back to proper location

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
infolurkCommented:
Its only possible if you have an incrementing field that you can select the max or top value from. Data is not stored in any particular order in SQL tables.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

rajeshprasathCommented:
the following code will help you to achieve your requirement,

FETCH
 [ [ NEXT | PRIOR | FIRST | LAST
 | ABSOLUTE { n | @nvar }
 | RELATIVE { n | @nvar }
 ]
 FROM
 ]
 { { [ GLOBAL ] cursor_name } | @cursor_variable_name }
 [ INTO @variable_name [ ,...n ] ]

refer this code also

DECLARE @name varchar(100)
DECLARE @dbId Int

DECLARE sample_cursor SCROLL CURSOR FOR select name, database_id from sys.databases

OPEN sample_cursor

FETCH FIRST FROM sample_cursor INTO @name, @dbId
print @name
print @dbId
print '-----'

FETCH LAST FROM sample_cursor INTO  @name, @dbId
print @name
print @dbId
print '-----'

CLOSE sample_cursor
DEALLOCATE sample_cursor

Open in new window

rajeshprasathCommented:
you need to declare your cursor as SCROLL CURSOR, then only this will work.
dteshomeCommented:
... according to the definition, LAST, returns the last row in the cursor and makes it the current row.
So, if you must process the records sequentially (not make last, current)  you can use the SELECT  statement used in the cursor declaration and do "SELECT Count(*) ..."  this will give the # of records that will be generated when the cursor is declared.  Once you have this value, declare an int variable, set it to the count, @MaxRecs. Also declare an int varoiable to keep track of the current count, @Count. Then, inside the while loop, check to see if @count = @MaxRecs, if so it is the last rec, process accordingly.  If not, increment @count process fetched rec etc ...
cyberkiwiCommented:
Rajesh,

STATIC works as well, among others, as shown in 2nd comment to the question.

A more detailed skeleton provided below.
With STATIC, you can also use @@CURSOR_ROWS (see link in 2nd comment above) without having to run a separate COUNT query.
declare @islast = 0
declare c cursor STATIC for ....
open c
fetch next from c into @var
while @@fetch_status = 0 begin
 -- test if we are on the last row
 fetch next from c into @var
 set @islast = case when @@fetch_status = -1 then 1 else 0 end
 fetch prior from c into @var  -- go back to proper location

 if @islast = 1 then ......

  -- rest of code

 fetch next from c into @var
end

Open in new window

NathanIrwinMining AnalystAuthor Commented:
Thanks for the quick responses, I will try some of these and get back to you
NathanIrwinMining AnalystAuthor Commented:
Thanks cyberkiwi.
I basically used a combination of you answers:
basically....

WHILE (@@FETCH_STATUS =0)
   modify first record
   FETCH NEXT
   modify the rest of the records
IF (@@FETCH_STATUS =-1)
   modify last record
END
END

Cheers
Nathan
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.