sql server 2008 - sp_send_dbmail stuck in loop sending hundreds of emails -

i have trigger set insert table , want have broker job scheduled send emails said table. have trigger working , thought had sp send emails working right loop gets stuck , sends hundreds of emails before cancel sp. thoughts on i've done wrong? i'm using batchemailid flag know needs sent , doesn't '0' = hasn't been sent , needs go , '1' = has been sent ignore.

create table:

set ansi_nulls on go  set quoted_identifier on go  set ansi_padding on go  create table [dbo].[tb_batchemail](     [batchemailid] [bit] null,     [to] [varchar](50) null,     [body] [varchar](255) null,     [subject] [varchar](20) null,     [profile] [varchar](50) null,     [orderid] [varchar](25) null,     [orderdatetime] [datetime] null,     [sentdatetime] [datetime] null ) on [primary]  go 

insert values:

    insert tb_batchemail values (     '0'     ,'someemail@address.com'     ,'msg body'     ,'test subject'     ,'dbmail profile'     ,'123456.001'     ,'6/4/2015'     ,null     ),     (     '0'     ,'someemail@address.com'     ,'msg body'     ,'test subject'     ,'dbmail profile'     ,'123456.002'     ,'6/4/2015'     ,null     ) 

sp_send_dbmail:

while 1=1  begin  declare @batchemailid bit declare @to varchar (25) declare @body varchar (250) declare @subject varchar (25) declare @profilename varchar (20)      select top 1             @batchemailid = batchemailid,            @to = [to],            @body = body,            @subject = [subject],            @profilename = [profile]     tb_batchemail     batchemailid = 0      if(@batchemailid null)     break;      exec msdb.dbo.sp_send_dbmail @recipients = @to             ,@body = @body             ,@subject = @subject             ,@profile_name = @profilename    update tb_batchemail     set batchemailid = 1, sentdatetime = getdate()     batchemailid = @batchemailid  end 

ok first off, update table, need unique value on each row.

alter table follows:

create table [dbo].[tb_batchemail](     [id] int identity(1,1),     [batchemailid] [bit] null,     [to] [varchar](50) null,     [body] [varchar](255) null,     [subject] [varchar](20) null,     [profile] [varchar](50) null,     [orderid] [varchar](25) null,     [orderdatetime] [datetime] null,     [sentdatetime] [datetime] null ) on [primary]  go 

the new [id] column used reference row in question.

the loop becomes (with test table used), comment out select, , uncomment email bit:

declare @tb_batchemail table (id int identity(1,1),                               batchemailid bit,                               [to] varchar(25),                               body varchar(250),                               [subject] varchar(25),                               [profile] varchar(20),                               sentdatetime datetime) insert @tb_batchemail select 0,'joe@domain.com','just test','test','myprofile',null  declare @id int declare @to varchar (25) declare @body varchar (250) declare @subject varchar (25) declare @profilename varchar (20)  while (select count(*) @tb_batchemail batchemailid=0) > 0 begin     select top 1              @id = id,             @to = [to],             @body = body,             @subject = [subject],             @profilename = [profile]     @tb_batchemail     batchemailid = 0      --exec msdb.dbo.sp_send_dbmail @recipients = @to     --      ,@body = @body     --      ,@subject = @subject     --      ,@profile_name = @profilename      select @id, @to, @body, @subject, @profilename      update @tb_batchemail     set batchemailid = 1, sentdatetime = getdate()     id = @id end