sp_send_dbmail stuck in loop sending hundreds of emails

Question

I have a trigger set up to insert into a table and then want to have a broker job scheduled to send emails from said table. I have the trigger working and I thought I had the SP to send emails working right but my loop gets stuck and sends hundreds of emails before I cancel the SP. Any thoughts on what I've done wrong? I'm using BatchEmailID as the flag to know what needs to be sent and what doesn't where '0' = it hasn't been sent and needs to go and '1' = it has been sent so 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 INTO 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]
    from tb_BatchEmail
    where BatchEmailID = 0

    if(@BatchEmailID is 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()
    where BatchEmailID = @BatchEmailID

end

Answer 1

OK first off, to update the table, you need a unique value on each row.

Alter your table as 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 will be used to reference back the row in question.

The your loop becomes this (with a test table used), just comment out my select, and uncomment the 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 into @tb_BatchEmail
select 0,'joe@domain.com','just a 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(*) from @tb_BatchEmail where BatchEmailID=0) > 0
begin
    select top 1 
            @Id = Id,
            @To = [To],
            @Body = Body,
            @Subject = [Subject],
            @ProfileName = [Profile]
    from @tb_BatchEmail
    where 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()
    where Id = @Id
end