CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-cursor
Volkan
Volkan

Reputation: 546

SQL cursor fetch status meaning

I don't understand the meaning of following lines:

   WHILE 1 = 1 
   BEGIN
      FETCH NEXT FROM SomeCursor INTO @SomeId, @SomeOtherColumn

      IF @@FETCH_STATUS <> 0 BREAK

What are the meanings of while 1=1? and if fetch status is different than 0?

Upvotes: 12

Views: 32810

Answers (3)

Bridge
Bridge

Reputation: 30651

1=1 is just a short condition which always return true, that is, loop forever (well, until you break out of it elsewhere).

As for fetch status values, as usual MSDN is your friend here. From https://msdn.microsoft.com/en-us/library/ms187308.aspx

  • 0 = The FETCH statement was successful.
  • -1 = The FETCH statement failed or the row was beyond the result set.
  • -2 = The row fetched is missing.

E.g. if you get anything other than 0, things have gone wrong, so there's no point in continuing.

On a side note, MSDN also notes that is is a legacy thing, @@FETCH_STATUS is global, and therefore where multiple cursors are being used it is not to be trusted. Instead lookup your cursor's individual fetch status value from the sys.dm_exec_cursors dynamic management function.

Upvotes: 13

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

WHILE 1=1 is the beginning of a loop and always results in a TRUE result, thus making the loop start (and potentially go on forever):

@@FETCH_STATUS points if there still are rows to be fetched (or have been fetched) from the cursor.

If there are still rows which can be fetched from the cursor, then @@FETCH_STATUS is 0, which shows SUCCESS.

If @@FETCH_STATUS is <> 0 (-1 or -2) then it points that there are no more rows that can be returned from the cursor and you have reached its end.

This is the usual condition used to break from a loop that is going over a cursor.

Just as @Lamak commented, here is the documentation for @@FETCH_STATUS.

Upvotes: 11

Siyual
Siyual

Reputation: 16917

This is a trick that is commonly used to avoid writing the FETCH NEXT line twice in the code. It starts an endless loop via WHILE 1 = 1 and continues until the @@FETCH_STATUS returns something other than 0, indicating that it has either reached the end of the cursor, or an error has occurred.

The possible @@FETCH_STATUS values are:

Return value    Description
0               The FETCH statement was successful.
-1              The FETCH statement failed or the row was beyond the result set.
-2              The row fetched is missing.

Upvotes: 7

Related Questions