Why it's an infinity loop?
sql-server, sql-server-2008, t-sql
Solution
This `select` will not assign a value to `@id` if `#t` is empty:
select top 1 @id = Id from #t
One way to fix it:
set @id = null
select top 1 @id = Id from #t
Problem
The following code will loop infinity. However, the table was empty after stop the query in SSMS. Changing `if @id is not null` to `if @@rowcount > 0` will get expected result. Why @id didn't get null value when `#t` is empty? ``` select 100 id into #t l: declare @id int select top 1 @id = Id from #t if @id is not null begin print @id --begin try raiserror ('Test error', 16, 10) --end try --begin catch --end catch delete from #t where Id = @id goto l end ``` ``` (1 row(s) affected) 100 Msg 50000, Level 16, State 10, Line 10 Test error (1 row(s) affected) --------- The row was deleted here. 100 Msg 50000, Level 16, State 10, Line 10 Test error (0 row(s) affected) 100 Msg 50000, Level 16, State 10, Line 10 Test error ...... ``` Update: Change `select top 1 @id = Id from #t` to `set @id = (select top 1 Id from #t)` works fine.