Concantenate email string within temptable loop

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Concantenate email string within temptable loop

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2013-01-31 : 11:26:56

I am tring to concantenate a string and send out less emails with multiple personnel in them. however my string never gets populated, its always empty when i look at it. so if I set this line like so
. set @Receipt = @Email + ';'

Itgets a single email and populates just fine but I want to send multiple personnel per mail and change like so but nothing is concantanated.
set @Receipt = @Receipt + @Email + ';'

Select @ii = COUNT(RowId) from #TempMail;
set @i = 1;
Set @Send = 1
While @i <= @ii
BEGIN
Select @Name = Name, @Email = Mail from #TempMail where Rowid = @i;
IF @Send < 50
BEGIN
set @Receipt = @Email + ';'
print @Send
print @Receipt
END
ELSE
BEGIN
--- do send the email here ...
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;
END

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-01-31 : 13:39:30

use

set @Receipt = @Receipt + @Email + ';'

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2013-02-01 : 09:15:07

hi,
Ok, I am using that as per my second paragraph but it still does not work, rather perplexed as to why this will not work. Dont want to send 1200 emails out of my sql server, would rather send out 20.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-01 : 09:21:50

I see you have PRINT statements for @Send and @Receipt immediately after your concatenation statement. What are you seeing in the printed output? NULLS? If it is NULL, are you initializing @Receipt to something? If not, insert a statement at the very beginning, immediately after the declaration.

SET @Receipt = '';

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources