Stroed Proc not sending emails properly

I created a stored Proc to send email in batches of 50 to people, but one oversite is that if the last group in under 50 they will not get the emails at all.  tried to corret but alls I get is errors theat @Receipt is missing or it does not send at all becuase of the @Send variable must be greater than 50 to send it.  So how can I resolve so i dont send out 1200 emails out of sql server but reduced to about 24 approx.

Select @ii = COUNT(RowId) from #TempMail;
      set @i = 1;
      Set @Send = 1
      set @Receipt = ''
            While @i <= @ii
              BEGIN
            Select @Name = Name, @Email = Mail from #TempMail where Rowid = @i;
            IF @Send < 3 and @i < @ii
                BEGIN
                   set @Receipt = @Receipt + @Email + ';'
                 END
            ELSE
                 BEGIN
            --- do send the email here ...
            set @Body = 'Test of Annual Hr'
            set @Subject = 'Test of Annual HR'
                                           
            EXEC   msdb.dbo.sp_send_dbmail
             @recipients = @Receipt,
             @body = @Body,
             @subject = @Subject,
             @profile_name = 'Testmail'
                                            
            Set @Send = 1;
            Set @Receipt = '';
             END
           set @Send = (@Send + 1)
           set @i = (@i + 1)
END
Drop table #TempMail;
kdeutschAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
try with some debugging?

Select @Subject = 'AnualHR Test' , @Body = @Name,@receipt=''
               
      Select @ii = COUNT(RowId) ,@i=1,@send=1 from #TempMail;
            While @i <= @ii
                  BEGIN
      Select      @receipt=@receipt+';'+@email from #TempMail
      where      Rowid between  @i and @i+49
                       
      Select @Name = Name, @i = @i+50 from #TempMail where Rowid = @i;
             
             select @receipt=stuff(@receipt,1,1,'')     -- remove first semi-colon

          print @i
          print @name
         print @receipt
                               
                   EXEC   msdb.dbo.sp_send_dbmail
                   @recipients = @Receipt,
                   @body = @Name,     --<  @name ????
                   @subject = @Subject,
                   @profile_name = 'Testmail'
                                           
                  Set @Receipt = '';
                  END
      Drop table #TempMail;
0
 
LowfatspreadCommented:
why don't you use my method as i provided on your last attempt,,,,

which doesn't loop to get the 50 but processes them in batches of 50 at a time...

and will deal with any number in the last batch

ID: 38843858
0
 
kdeutschAuthor Commented:
Ok, I will go back and try it out, just used 1st solution becuase thats what my question and error was about, given everyone thier fair due.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Anthony PerkinsCommented:
From reading your code it appears that if @Send < 3 and @i < @ii then no email is sent out.
Is that what you intended?
0
 
kdeutschAuthor Commented:
lowfatSpread,

Ok tried the code you had posted in the question befor ethis one and it errors out on me,
it gieves  the following error

Msg 14624, Level 16, State 1, Procedure sp_send_dbmail, Line 260
At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients".

(1 row(s) affected)


Here is the code
Select @Subject = 'AnualHR Test' , @Body = @Name,@receipt=''
               
      Select @ii = COUNT(RowId) ,@i=1,@send=1 from #TempMail;
            While @i <= @ii
                  BEGIN
      Select      @receipt=@receipt+';'+@email from #TempMail
      where      Rowid between  @i and @i+49
                        
      Select @Name = Name, @i = @i+50 from #TempMail where Rowid = @i;
             
             select @receipt=stuff(@receipt,1,1,'')     -- remove first semi-colon
                               
                   EXEC   msdb.dbo.sp_send_dbmail
                   @recipients = @Receipt,
                   @body = @Body,
                   @subject = @Subject,
                   @profile_name = 'Testmail'
                                           
                  Set @Receipt = '';
                  END
      Drop table #TempMail;
0
 
kdeutschAuthor Commented:
HI,
It prints out

51
My Name

Then error
Msg 14624, Level 16, State 1, Procedure sp_send_dbmail, Line 260
At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients".

(1 row(s) affected)
0
 
kdeutschAuthor Commented:
Ok figured it out

I replaced

 Select      @receipt=@receipt+';'+ @email from #TempMail
where      Rowid between  @i and @i+49

With
 Select      @receipt=@receipt+';'+ Mail from #TempMail
 where      Rowid between  @i and @i+49

@email was not being set on temp mail it was mail.  It works now.
0
 
LowfatspreadCommented:
what is/how is rowid defined?

what does select min(rowid) from #TempMail   return?
0
 
kdeutschAuthor Commented:
Thanks works great.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.