TSQL – Loop Through Result Set WITHOUT Cursors

There is a time and a place for everything, cursors included. Here is one way to avoid using them.

To NOT use cursors, you must create a temp table with a "flag" column to indicate whether or not it has been processed yet. Then, rather than updating the cursor, you update the flag column. Here's an example:

DECLARE @ReportName varchar(200);

/* Create a table with a PROCESSED column to track what row has/has not been processed yet. */
CREATE TABLE #temp_reports
( REPNAME VARCHAR(100)
,PROCESSED BIT DEFAULT 0);

/* Select data into your temp table */
INSERT INTO #temp_reports(REPNAME)
SELECT REPNAME FROM REPORTS

/* Make a while loop that continues while any PROCESSED values still equal 0. */
WHILE (SELECT TOP 1 COUNT(*) FROM #temp_reports WHERE PROCESSED = 0) > 0
BEGIN
SELECT TOP 1 @ReportName = REPNAME
FROM #temp_reports WHERE PROCESSED = 0;

/* Do any actions required with your variable... */
PRINT @ReportName;

/* Update your temp table (if you forget this your loop will run forever) */
UPDATE #temp_reports SET PROCESSED = 1
WHERE REPNAME = (
SELECT TOP 1 REPNAME FROM #temp_reports
WHERE REPNAME = @ReportName AND PROCESSED = 0
)
END