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.
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.
1This 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"
2And, 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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With