Cursor was created for processing one record on a time.
Before you use a CURSOR, you’ll need to declare it. The basic form looks like:
DECLARE cursorName CURSOR for SELECT field1, field2 FROM myTable WHERE whereClause
Once we have the cursor declared, we need to OPEN it to cause it to execute:
The rest is going to look very similar to what we did back when we were using ODBC. To move to the first record, we have to FETCH the row:
FETCH NEXT FROM cursorName INTO @field1, @field2
There is a special variable in SQL called @@FETCH_STATUS that will be zero (0) as long as the fetch was able to retrieve a row, so you’ll want to set up a while loop after your first fetch that checks the status and then processes the data. Right before the end of the while loop you will issue another FETCH.
(SQL Server) FETCH NEXT FROM cursorName INTO @field1, @field2 While @@FETCH_STATUS = 0 Begin /* do something with the data here */ FETCH NEXT FROM cursorName INTO @field1, @field2 End
DECLARE cursor-name INSENSITIVE (DYNAMIC)SCROLL CURSOR WITH HOLD FOR Select Statement FOR UPDATE OF FOR READ ONLY Insensitive - Once a cursor is opened it does not have any sensitivity to inserts, updates or adds performed by this or any other activation group. Scroll - The cursor is scrollable, you can get rows in reverse order and reposition to where your next read will occur. Dynamic Scroll - The cursor is update-able. With Hold - Prevents the cursor from being closed as a consequence of a commit operation. (when join multiple tables, it will be read only by default.) FETCH Next FROM cursor-name (single fetch) INTO Host Variable/Structure Prior (multiple row fetch) FOR Host Variable or Integer ROWS INTO Host Structure Array First Last Before After Current Relative - host variable or integer