Only get 1 row from a select cursor stored procedure

rss2
rss2 used Ask the Experts™
on
Hello,
I'm new to cursors and I'm having a really hard time understanding them.

I have a vb app form, on which there are navigational buttons with which I would like to move through a recordset.

I created a cursor with a simple select statement which retrieves only 1 cursor, which I understand is forward-only due to .MoveLast not working ("Rowset does not support fetching backward").

Here is the stored proc:
CREATE PROCEDURE sp_Charts_GetQ
AS
declare sp_Charts_GetQ_cursor cursor
global
scroll
--dynamic --error on rs.MoveNext
keyset
optimistic
type_warning
for select CHART.* from CHART order by CHART.Series, CHART.[Sheet Name], CHART.[Sheet No], CHART.[Part No]

open sp_Charts_GetQ_cursor
fetch next from sp_Charts_GetQ_cursor
close sp_Charts_GetQ_cursor
deallocate sp_Charts_GetQ_cursor
GO

How do I get the next row from the cursor when I call [recordset].MoveNext?

How do I get the last row from the cursor when I call [recordset].MoveLast? Etc..

Thank you very much indeed.

By the way, why are cursors so much better than embedded SQL?

rss2
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
...
open sp_Charts_GetQ_cursor
fetch next from sp_Charts_GetQ_cursor

while @@fetch_status=0
begin
  .../* process the row */
  fetch next from sp_Charts_GetQ_cursor
end

.
.
.
close sp_Charts_GetQ_cursor
deallocate sp_Charts_GetQ_cursor

Author

Commented:
I'm getting "Item cannot be found in the collection corresponding to the requested name or ordinal. 3265 etc.."

Since I asked this question, I had changed the stored proc to include
--declare
--@ls_Chart_Series nvarchar(25),
--@ls_Chart_SheetName nvarchar(12)

before the open line and
fetch next from sp_Charts_GetQ_cursor into @ls_Chart_Series, @ls_Chart_SheetName

before closing and deallocating the cursor. But I've removed those lines from the stored proc now, and made it exactly as you've written.

When I run the stored proc from within Query Analyzer, those two columns and one row is returned.

Why do you think that is?

Also, if I call [recordset].MoveLast or MoveNext, will it go to the new row in the table?
Top Expert 2012
Commented:
If you are already using a recordset than there is no need for a cursor in the Stored Proc (and in any case Cursors should only be used as a last resort), in other words change your Stored Proc as follows:

CREATE PROCEDURE sp_Charts_GetQ
AS

select CHART.*
from CHART
order by CHART.Series, CHART.[Sheet Name], CHART.[Sheet No], CHART.[Part No]

GO

Anthony
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
That still doesn't allow [recordset].MovePrevious or [recordset].MoveLast. How do I change the cursor type in the recordset returned by the stored procedure like the one above (the one without a cursor)?

Thank you.

Author

Commented:
How would I page through the recordset returned by
CREATE PROCEDURE sp_Charts_GetQ
AS

select CHART.*
from CHART
order by CHART.Series, CHART.[Sheet Name], CHART.[Sheet No], CHART.[Part No]

GO
?

In the VB app, I can do .MoveNext but not .MovePrevious or .MoveLast.

Any ideas?

Thanks.
Top Expert 2012

Commented:
>>In the VB app, I can do .MoveNext but not .MovePrevious or .MoveLast.<<
(This topic can be better covered in the Visual Basic Topic Area) By default, a Stored Procedure returns a Forward Only cursor (you can confirm this by checking the rs.CursorType after the Execute method you will notice that it has a value of adOpenForwardOnly = 0).  By the way as an aside, with a ForwardOnly cursor you can do a MoveLast, but not MoveFirst (or MovePrevious).

Post your VB code and we can suggest what you need to change in order for you to be able to use MovePrevious and MoveFirst.

Anthony

Author

Commented:
Thank you for your help. I changed the stored procedure from using a cursor to just returning a recordset from a simple select statement (suggested above). With an ADODB.Command object. I was then able to call the stored procedure and set the cursor from forward-only to Openkeyset and the LockType to Optimistic. I didn't realise I could call a stored proc with a command object.

I'm rewarding the points to Anthony.

Thank you very very much! Your suggestion got me on the right track.

-Rebecca

Author

Commented:
A returned recordset back to the calling application was ideal, as I could then set the cursor and lock types using the command object. My cursor was only returning one row at a time, and with a forward-only cursor at that; clearly not the best option. Anthony suggested placing a simple select statement in the stored proc, which got me on the right track to what I needed to do.

Thank you!!!

-Rebecca

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial