SQL: Fetching Records One-by-One Without Using a Cursor

Fetching Records One-by-One Without Using a Cursor

The following example shows how to process records one-by-one without using a cursor.

DECLARE @EMP_ID CHAR(11);

SET ROWCOUNT 0;

SELECT *
INTO #MYTEMP
FROM Employee;

SET ROWCOUNT 1;

SELECT @EMP_ID = EMP_ID
FROM #MYTEMP;

WHILE @@ROWCOUNT <> 0
BEGIN
SET ROWCOUNT 0;

SELECT *
FROM #MYTEMP
WHERE EMP_ID = @EMP_ID;

DELETE FROM #MYTEMP
WHERE EMP_ID = @EMP_ID;

SET ROWCOUNT 1;

-- Set one ID to @EMP_ID from #MYTEMP
SELECT @EMP_ID = EMP_ID
FROM #MYTEMP;
END;

SET ROWCOUNT 0;

Remarks

Cursors are generally avoided when they are not required because row-by-row processing can be expensive compared with set-based operations.

In the above example, a temporary table is used to process the records one-by-one without explicitly declaring a cursor.

`SET ROWCOUNT 0` allows statements to process all applicable rows, while `SET ROWCOUNT 1` limits the number of rows processed by the statement to one.

The query uses this behavior to retrieve one `EMP_ID` at a time, process that record, delete it from the temporary table, and then retrieve the next `EMP_ID`.

This approach can be useful for specific requirements where records need to be processed individually without using a cursor.
 

Note: SET ROWCOUNT should be used with caution in new development. Microsoft recommends using TOP or OFFSET/FETCH where appropriate to limit the number of rows. Microsoft also advises against using SET ROWCOUNT with DELETE, INSERT, and UPDATE in new development.