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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With