sqlguy-736318 (7/27/2015)
Hi, can I safely use top n select/delete in a while loop? For example:declare @FieldVal int
while (select count(*) from @MyTempTable) > 0
begin
select top 1 @FieldVal = FieldVal from @MyTempTable
-- process @FieldVal then delete the row
delete top 1 from @MyTempTable
end
I like the simplicity of the above approach as long as it's reliable and there aren't any gotchas that I may not be aware of.
No, it is not safe. This is because neither the SELECT nor the DELETE have an ORDER BY clause on a unique column set... so it's not defined which row will be either selected or deleted. I'd suggest adding an identity column to the table variable, selecting that out into a variable, and then deleting based upon that.
However, loops would be slow. I'd recommend trying to make this more set-based for performance reasons. There are people here that could help you out with that. Just follow the instructions in the first link in my signature for how to post your stuff so that people will want to help you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes