For Loop

I have a table which has an identity column. I want to traverse through the table one row at a time using FOR Loop. Can someone help me with the syntax.

DECLARE @.i INT
SET @.i = 1

WHILE EXISTS(SELECT 1 FROM #Test WHERE id > @.i)
BEGIN
SELECT * FROM #Test WHERE id = @.i
SET @.i = @.i + 1
END

|||

Thank you for the reply Sir. But in the while loop you are giving a select statement. I guess it will perform a table scan which may slow down the process. I am not sure whether I am right or not. If I am right, then is there any alternative condition which can be incorporated in the while loop?

Thanks

Subhojeet

|||Due to the fact that this is a temporary table and not a table variable you can add a index to the table to avoid scans. But keep in mind that in small tables table scans can be faster for the query optimizer than doing a index scan.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Could you please explain what you are trying to do in the loop? You seem to be concerned about the EXISTS query performance but you are doing row-by-row processing which will be slow anyway. If you describe your problem with sample schema/data and expected results it will be easier to suggest a set-based solution.