Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-08-23
9
Medium Priority
?
2,993 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:NathanIrwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 11

Expert Comment

by:Larissa T
ID: 33507665
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
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 33507686
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

0
 
LVL 8

Expert Comment

by:infolurk
ID: 33507687
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Expert Comment

by:rajeshprasath
ID: 33507726
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

0
 
LVL 2

Expert Comment

by:rajeshprasath
ID: 33507735
you need to declare your cursor as SCROLL CURSOR, then only this will work.
0
 

Expert Comment

by:dteshome
ID: 33507755
... 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 ...
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 2000 total points
ID: 33507770
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

0
 
LVL 7

Author Comment

by:NathanIrwin
ID: 33507834
Thanks for the quick responses, I will try some of these and get back to you
0
 
LVL 7

Author Closing Comment

by:NathanIrwin
ID: 33507903
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
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question