cursors question

I'll greatly appreciate any help with the error message bellow. What I am
trying to do is first I am getting a customer type and nr of members of that
type. Next I list first 5 customers and if there are more than 5 I want a
message to appear indicating how many more records are remaining and the las
t
record. Here is what I get:
3 J, Total nr. of records 5
1 R, M
2 H, S
3 S, S
4 W, G
5 W, P
4 O, Total nr. of records 1
1 V, D
5 R, Total nr. of records 17
1 M, M
2 V, J
3 R, R
4 D, K
5 S, M
Server: Msg 16911, Level 16, State 1, Line 39
fetch: The fetch type last cannot be used with forward only cursors.
.. There are 12 more records
-->> The last record being S, S
My code looks something like this:
declare @.MemID varchar(10)
declare @.total int
declare @.RowNum int
declare @.NamesNum int
declare @.RowNum1 int
declare @.fname varchar(20)
declare @.lname varchar(20)
declare MemList cursor for
select benefit, count(*) total from cust where branch='xxx' group by benefit
OPEN MemList
FETCH NEXT FROM MemList
INTO @.MemID, @.total
set @.RowNum = 0
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.RowNum = @.RowNum + 1
print cast(@.RowNum as varchar) + ' ' + LEFT(@.MemID, 1) + ', Total nr. of
records ' + cast(@.total as varchar)
---
declare namesList cursor
FOR select surname, name from cust where branch='xxx' and benefit=@.MemID
OPEN namesList
FETCH NEXT FROM namesList
INTO @.lname, @.fname
set @.RowNum1 = 0
WHILE @.@.FETCH_STATUS = 0 AND @.RowNum1 < 5
BEGIN
set @.RowNum1 = @.RowNum1 + 1
print ' ' + cast(@.RowNum1 as varchar) + ' ' + LEFT(@.lname,1) + ', ' +
@.fname
FETCH NEXT FROM namesList
INTO @.lname, @.fname
END
IF @.total >= 6
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH LAST FROM namesList
INTO @.lname, @.fname
PRINT ' ... There are ' + ' ' + cast((@.total - 5) as varchar) + ' more
records'
PRINT ' -->> The last record being ' + LEFT(@.lname, 1) + ', ' + @.fname
END
CLOSE namesList
DEALLOCATE namesList
---
FETCH NEXT FROM MemList
INTO @.MemID, @.total
END
CLOSE MemList
DEALLOCATE MemList
Thanks for your help!Hi,
In Order to Use the LAST function you have to declare the Cursor as a
Scroll Cursor.
Please see code below - take note in a slight modification to stop the
Cursor repeating itself.
HTH
Barry
SQL CODE:
declare @.MemID varchar(10)
declare @.total int
declare @.RowNum int
declare @.NamesNum int
declare @.RowNum1 int
declare @.fname varchar(20)
declare @.lname varchar(20)
declare MemList cursor for
select benefit, count(*) total from cust where branch='xxx' group by
benefit
OPEN MemList
FETCH NEXT FROM MemList
INTO @.MemID, @.total
set @.RowNum = 0
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.RowNum = @.RowNum + 1
print cast(@.RowNum as varchar) + ' ' + LEFT(@.MemID, 1) + ', Total
nr. of records ' + cast(@.total as varchar)
---
declare namesList scroll cursor -- Declare the Cursor as a Scroll
Cursor
FOR select surname, name from cust where branch='xxx' and
benefit=@.MemID
OPEN namesList
FETCH NEXT FROM namesList
INTO @.lname, @.fname
set @.RowNum1 = 0
WHILE @.@.FETCH_STATUS = 0 AND @.RowNum1 < 5
BEGIN
set @.RowNum1 = @.RowNum1 + 1
print ' ' + cast(@.RowNum1 as varchar) + ' ' + LEFT(@.lname,1) +
', ' +
@.fname
FETCH NEXT FROM namesList
INTO @.lname, @.fname
END
--IF @.total >= 6 -- Remove this from here...
WHILE @.@.FETCH_STATUS = 0 And @.Total >= 6 ... and put it here...
BEGIN
FETCH LAST FROM namesList
INTO @.lname, @.fname
PRINT ' ... There are ' + ' ' + cast((@.total - 5) as varchar) + ' more
records'
PRINT ' -->> The last record being ' + LEFT(@.lname, 1) + ', ' +
@.fname
Set @.total = 0 --Set The Total to Zero so this loop finishes
END
CLOSE namesList
DEALLOCATE namesList
---
FETCH NEXT FROM MemList
INTO @.MemID, @.total
END
CLOSE MemList
DEALLOCATE MemList|||Why use a cursor to do all that formatting rather than just output a
query? Have you looked up the CUBE / ROLLUP operator in Books Online?
It would help you with the total counts.
If you want help to write a query instead of a cursor then please post
DDL and sample data. Cursors are rarely a good idea and surely not for
this - there really are much better ways to write reports.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||hehe!
I was going to maybe post an example to use rather than a cursor -- but
I haven't really got the time!
And without any DDL - I definitely don't have time... ;-)
Barry|||This could be close to what you want but it is untested. It should give
you the first 5 customers per benefit. I've no way of knowing what you
mean by "first" and "last" so I've assumed alphabetical order by name.
You didn't specify ORDER BY so the order you will get from your cursor
is undefined and may be unreliable.
SELECT C.benefit, C.surname, C.name, B.row_cnt, B.last_cust
FROM cust AS C,
(SELECT benefit, COUNT(*) AS row_cnt,
MAX(surname+','+name) AS last_cust
FROM cust
GROUP BY benefit) AS B
WHERE branch='xxx'
AND 5 <=
(SELECT COUNT(*)
FROM cust
WHERE surname+','+name <
C.surname+','+C.name)
AND B.benefit = C.benefit
ORDER BY C.benefit, C.surname, C.name ;
The pretty formatting you can do at the front end or in a reporting
tool.
Hope this helps.
David Portas
SQL Server MVP
--|||Correction. Change "<=" to ">="
David Portas
SQL Server MVP
--