TSQL – Loop Through Result Set WITHOUT Cursors

There is some controversy about using cursors in TSQL.  Overall, I believe they tend to tie up resources and are seen as bad.  So how do you get around using them?  Here’s how.

You can see how cursors are used in my post at TSQL – Simple Loop Through Result Set Using Cursor

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:

Here’s a local version in case there’s a web apocalypse:

 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