sql Concantenating not working properly

Posted on 2013-02-01
Last Modified: 2013-02-01
So the objective is not to send out 1200 emails form my sql server but only send out 20 by concantenating the emails together, but when I run the proc below it does not concantonate them it is blank, but if I change to individual send out it works just fine.  This should work but its not working one bit, what amd I doing wrong here.  So what I am doing is counting 50 emails, concantonating them and then sending it out, but no emails get concatenated.

Select @ii = COUNT(RowId) from #TempMail;
      set @i = 1;
      Set @Send = 1
            While @i <= @ii
             Select @Name = Name, @Email = Mail from #TempMail where Rowid = @i;
                  IF @Send < 50
                          set @Receipt = @Receipt + @Email + ';'
                      --- do send the email here ...
                   Set @Subject = 'AnualHR Test'
                  Set @Body = @Name
                   EXEC   msdb.dbo.sp_send_dbmail
                   @recipients = @Receipt,
                   @body = @Body,
                   @subject = @Subject,
                   @profile_name = 'Testmail'
                  Set @Send = 1;
                  Set @Receipt = '';
            set @Send = (@Send + 1)
            set @i = (@i + 1);
      Drop table #TempMail;
Question by:kdeutsch
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 33

Accepted Solution

knightEknight earned 500 total points
ID: 38843793
Did you initialize the @Receipt variable to '' ?  (Do this before entering the loop)

Also, make sure you declare it long enough to hold 50 addresses:

declare @Receipt varchar(8000) = ''
LVL 21

Expert Comment

ID: 38843811
Yes, if you don't initialize @Receipt it will be null, and concatenating anything with null results in null
LVL 50

Expert Comment

ID: 38843858
more like this

declare @recipt varchar(8000)

   Select @Subject = 'AnualHR Test' 
                ,@Body = @Name
Select @ii = COUNT(RowId) ,@i=1,@send=1
  from #TempMail;
            While @i <= @ii
             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 = '';
      Drop table #TempMail;

Open in new window


Author Comment

ID: 38843979

Well, it was that i did not inialize the variable @Receipt, I called it out but forgot to inialize it.  But while looking at this here is my problem I want to send in groups of 50, but what happens if the last group is below 50, those personnel will not get the email, so this will really not work as some personnel will not get the email.

Author Closing Comment

ID: 38844066
Thanks, thought I initalized and totally over looked it.

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
This query failed in sql 2014 5 39
Present Absent from working date rage 11 49
TSQL recursive CTE challenge... 8 34
How can I get the entire database script? 7 25
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question