NathanIrwin
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 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
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
you need to declare your cursor as SCROLL CURSOR, then only this will work.
... 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 ...
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 ...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the quick responses, I will try some of these and get back to you
ASKER
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
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
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