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.
