How to know last row when iterating cursor on SQL Server?

Question

How to know last row when iterating cursor on SQL Server?

I have tried with counter and @@CURSOR_ROWS and @@FETCH_STATUS, but it doesn't work well or I didn't use them in proper way.

EDIT:

Well, yeah, composing string with comma... , on the end of each loop. Of course, before return I can cut off the last character which will be , but I'm wondering is this only way?

However, there is a lot of situations, it may be good to know how to recognise the last row.

Answer 1

As suggested in the comments, putting commas between elements is far more easily realised by doing something special for the first row, rather than the last.^1,2

Rather than

WHILE @@FETCH_STATUS=0
BEGIN
    SET @value = @value + @newelement + ','

    ...
END

And trying to work out how to treat the last row specially, it's far easier to do:

SET @first = 1
WHILE @@FETCH_STATUS=0
BEGIN
    IF @first = 1
        SET @first = 0
    ELSE
        SET @value = @value + ','

    SET @value = @value + @newelement

    ...
END

---

An alternative transformation, if you really do need to do something for the last row is to use intermediate variables and a non-standard cursor loop:

declare boris cursor for select top 10 name from sys.objects order by name

declare @name sysname
declare @nametemp sysname

open boris
fetch next from boris into @nametemp
while @@FETCH_STATUS=0
BEGIN
    set @name = @nametemp --Use SELECT if multiple variables to be assigned

    fetch next from boris into @nametemp

    if @@FETCH_STATUS!=0
    begin
        print 'and finally'
    end

    print @name
END

close boris
deallocate boris

Which prints (for me, in a nearly empty DB):

EventNotificationErrorsQueue
filestream_tombstone_2073058421
filetable_updates_2105058535
IsIt
QueryNotificationErrorsQueue
queue_messages_1977058079
queue_messages_2009058193
queue_messages_2041058307
ServiceBrokerQueue
and finally
sysallocunits

But I'd strongly recommend the first type of transformation instead.

---

¹This isn't just a T-SQL thing either. In most programming languages, it's usually far easier if you can transform your problem from "treat the last iteration differently" into "treat the first iteration differently"

²And, of course, insert usual warnings about cursors being a last resort in SQL, use set-based approaches unless or until they're proved to be inadequate.