Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why it's an infinity loop?

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.

like image 899
ca9163d9 Avatar asked Feb 17 '23 01:02

ca9163d9


1 Answers

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 
like image 165
Andomar Avatar answered Mar 04 '23 05:03

Andomar