Control of flow question

[John Shahan]

I would like to be able to have a "while" statement execute until all af the rows in a temporary table have be processed.

I know I can do this with counters.

However, I was wondering if anyone knows of a SQL equivalent of
"WHILE NOT EOF" as in "do this until you hit the end of the table".

Thanks in advance for any input.

[KingSexy182]

BOL - WHILE (described), also have a read on @@FETCH_STATUS.

B. Using WHILE within a procedure with cursors
The following WHILE construct is a section of a procedure named count_all_rows. For this example, this WHILE construct tests the return value of @@FETCH_STATUS, a function used with cursors. Because @@FETCH_STATUS may return -2, -1, or 0, all three cases must be tested. If a row is deleted from the cursor results since the time this stored procedure was executed, that row is skipped. A successful fetch (0) causes the SELECT within the BEGIN...END loop to execute.

USE pubs
DECLARE tnames_cursor CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
--SET @tablename = 'authors'
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @tablename = RTRIM(@tablename)
EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '
+ @tablename )
PRINT ' '
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

[KingSexy182]

Heres a simpler query from BOL.


DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees

OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor


- it will keep fetching a record from the employees table until the @@fetch_status doesnot = 0, and if successful it will execute the transaction. Becareful when @@fetch_status = -2 (missing - if row was deleted during the cursor.)

[John Shahan]

Thanks much for the detailed response and I'm sure this would work.

I'm a little gunshy about using a cursor, though, for performance reasons.

[KingSexy182]

Cursors with SQL 2000 is pretty good.
It is much better and quicker than in 6.5 => thats what ive found out.

Consider putting nolocks on the table when declaring the cursor.

[MAK]

and also fast forward cursor