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.

Solution

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.