How to loop over a query result set in SQL Server T-SQL?

In SQL Server T-SQL, you can use CURSOR to loop over a query result set as below:

DECLARE @id INT
DECLARE @getid CURSOR

SET @getid = CURSOR FOR
SELECT id FROM dept

OPEN @getid
FETCH NEXT
FROM @getid INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
    --other statements using @id
    FETCH NEXT
    FROM @getid INTO @id
END

CLOSE @getid
DEALLOCATE @getid

@@FETCH_STATUS function returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection. It returns an integer value as given below.

0 = The FETCH statement was successful.

-1 = The FETCH statement failed or the row was beyond the result set or end of the record.

-2 = The row fetched is missing.

Because @@FETCH_STATUS is global to all cursors on a connection, use it carefully. After a FETCH statement executes, the test for @@FETCH_STATUS must occur before any other FETCH statement executes against another cursor. @@FETCH_STATUS is undefined before any fetches have occurred on the connection.

For example, a user executes a FETCH statement from one cursor, and then calls a stored procedure that opens and processes results from another cursor. When control returns from that called stored procedure, @@FETCH_STATUS reflects the last FETCH executed inside that stored procedure, not the FETCH statement executed before the call to the stored procedure.

The Syntax for SET statement to specify a cursor variable:

SET     
{ @cursor_variable =   
    { @cursor_variable | cursor_name   
    | { CURSOR [ FORWARD_ONLY | SCROLL ]   
        [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]   
        [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]   
        [ TYPE_WARNING ]   
    FOR select_statement   
        [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]   
      }   
    }  
} 

CURSOR
Specifies that the SET statement contains a declaration of a cursor.

SCROLL
Specifies that the cursor supports all fetch options: FIRST, LAST, NEXT, PRIOR, RELATIVE, and ABSOLUTE. You can't specify SCROLL when you've also specified FAST_FORWARD.

FORWARD_ONLY
Specifies that the cursor supports only the FETCH NEXT option. The cursor is retrieved only in one direction, from the first to the last row. When you specify FORWARD_ONLY without the STATIC, KEYSET, or DYNAMIC keywords, the cursor is implemented as DYNAMIC. If you don't specify either FORWARD_ONLY or SCROLL, FORWARD_ONLY is the default, unless you specify the keywords STATIC, KEYSET, or DYNAMIC. For STATIC, KEYSET, and DYNAMIC cursors, SCROLL is the default.

STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb. As a result, modifications made to the base tables after the cursor is opened aren't reflected in the data returned by fetches made to the cursor. And, this cursor doesn't support modifications.

KEYSET
Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into the keysettable in tempdb. Changes to nonkey values in the base tables, either made by the cursor owner or committed by other users, are visible as the cursor owner scrolls around the cursor. Inserts made by other users aren't visible, and inserts can't be made through a Transact-SQL server cursor.

If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key values from outside the cursor are similar to a delete of the old row followed by an insert of the new row. The row with the new values isn't visible, and tries to fetch the row with the old values return an @@FETCH_STATUS of -2. The new values are visible if the update happens through the cursor by specifying the WHERE CURRENT OF clause.

DYNAMIC
Defines a cursor that reflects all data changes made to the rows in its result set as the cursor owner scrolls around the cursor. The data values, order, and membership of the rows can change on each fetch. The absolute and relative fetch options aren't supported with dynamic cursors.

FAST_FORWARD
Specifies a FORWARD_ONLY, READ_ONLY cursor with optimizations enabled. FAST_FORWARD can't be specified when SCROLL is also specified.

READ_ONLY
Prevents updates from being made through this cursor. The cursor can't be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.

SCROLL LOCKS
Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. SQL Server locks the rows as they're read into the cursor to guarantee their availability for later modifications. You can't specify SCROLL_LOCKS when FAST_FORWARD is also specified.

OPTIMISTIC
Specifies that positioned updates or deletes made through the cursor don't succeed if the row was updated since being read into the cursor. SQL Server doesn't lock rows as they're read into the cursor. Instead, it uses comparisons of timestamp column values, or a checksum value, if the table has no timestamp column, to determine if the row was modified after being read into the cursor. If the row was modified, the attempted positioned update or delete fails. You can't specify OPTIMISTIC when FAST_FORWARD is also specified.

TYPE_WARNING
Specifies that a warning message is sent to the client when the cursor is implicitly converted from the requested type to another.

FOR select_statement
Is a standard SELECT statement that defines the result set of the cursor. The keywords FOR BROWSE, and INTO aren't allowed within the select_statement of a cursor declaration.

If you use DISTINCT, UNION, GROUP BY, or HAVING, or you include an aggregate expression in the select_list, the cursor is created as STATIC.

If each underlying table doesn't have a unique index and an ISO SCROLL cursor or if a Transact-SQL KEYSET cursor is requested, the cursor is automatically a STATIC cursor.

If select_statement contains an ORDER BY clause in which the columns aren't unique row identifiers, a DYNAMIC cursor is converted to a KEYSET cursor, or to a STATIC cursor if a KEYSET cursor can't be opened. This process also occurs for a cursor defined by using ISO syntax but without the STATIC keyword.

READ ONLY
Prevents updates from being made through this cursor. The cursor can't be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated. This keyword varies from the earlier READ_ONLY by having a space instead of an underscore between READ and ONLY.

UPDATE [OF column_name[ ,... n ] ]
Defines updatable columns within the cursor. If OF column_name [,...n] is supplied, only the columns listed allow modifications. When no list is supplied, all columns can be updated, unless the cursor has been defined as READ_ONLY.