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
 General SQL Server Forums
 New to SQL Server Programming
 PRINT statement changes execution

Author  Topic 

spareus
Yak Posting Veteran

52 Posts

Posted - 2013-06-14 : 10:58:06
I am facing a strange problem.
I dont want to attampt send mail if mail id of ctx is not found or recepient is null in my table. For this I am using if else block.
If I use a print statement immediately after if statement, all mails are sent even if recepient is null (mail sent to bcc recepient). Each mail sent is marked to a bcc also.

If I comment out print statement, query hangs with endless messages that ctx abcd is not found.

Using SQL server 2012.

Pl help.
My code is as below.
=================================================================

--use cpcom
DECLARE @date DATE
declare @mailcount int
declare @recipients nvarchar(max)
declare @ctx nvarchar(255)
declare @subject nvarchar(255)
--declare @date date
set @date='2013-06-12'
set @mailcount=0
if @date is null OR @DATE=''
begin
set @date = getdate()
end
print @date


truncate table tmpsr
insert into tmpsr
select * from srcp where @date = date
select @ctx = min(ctx) from TMPSR

SET @subject = 'Your billing details - ' + (FORMAT(@date, 'dd MMM yyyy'))

while @ctx is not null
begin
DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML ='abc'


IF exists (
select ctx from CPM where ctx=@ctx)
BEGIN
PRINT 'ctx ' + @ctx + ' FOUND IN MAILID'


set @recipients = (select [CPMailid] from CPM where ctx = @ctx)
if @recipients <>'' and @recipients is not null
print @recipients -- all mails sent but remove this line and query hangs by repeating same output
begin

EXEC msdb.dbo.sp_send_dbmail @recipients=@recipients,
@subject = @subject,
@body = @tableHTML,
@blind_copy_recipients = 'abc@def.com',
@body_format = 'HTML' ;



UPDATE SRCP

SET SRCP.[MAILED]='YES',[EMAIL ID]=@recipients, [MAIL DATE]=GETDATE(),MailItemID=(SELECT mailitem_id FROM msdb.dbo.sysmail_allitems WHERE mailitem_id= (SELECT MAX(DISTINCT(mailitem_id)) FROM msdb.dbo.sysmail_allitems ))
WHERE SRCP.ctx=@ctx AND SRCP.[DATE]=@date;

select @ctx = min(ctx) from [TMPSR] where ctx > @ctx

set @mailcount= @mailcount + 1
end
END
ELSE
BEGIN
PRINT 'ctx ' + @ctx + ' NOT FOUND IN MAILD ID'
select @ctx = min(ctx) from [TMPSR] where ctx > @ctx
END
end
--END

print 'total ' + RTRIM(CONVERT(varchar(30), @mailcount)) + ' mails sent to CP today'


DECLARE @body varchar(max)
SET @subject = 'total ' + RTRIM(CONVERT(varchar(30), @mailcount)) + ' mails sent to CP today'
SET @body = 'total ' + RTRIM(CONVERT(varchar(30), @mailcount)) + ' mails sent to CP today'
EXEC msdb.dbo.sp_send_dbmail @recipients='KET@GMAIL.COM',
@subject = @subject ,
@body = @body,
@body_format = 'HTML' ;
====================================================================

Regards,


Regards,
Spareus

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-14 : 12:54:05
I think I see the "issue", but that's the logic you've implemented.
if @recipients <>'' and @recipients is not null
print @recipients -- all mails sent but remove this line and query hangs by repeating same output
begin
..
end
That will apply teh if logic: IF "@recipients <>'' and @recipients is not null" THEN PRINT. After that you have a block (BEGIN..END) that will always execute. You could move the print into the BEGIN block or before the IF statement.
Go to Top of Page

spareus
Yak Posting Veteran

52 Posts

Posted - 2013-06-17 : 04:44:20
Hi Lamprey,
PRINT statement is there just for the debugging purpose.
I have tried your suggession by moving the same within the begin block.
There is no change in execution. It still go for endless loop.
This all started when I put a mail id in bcc. If I remove bcc, code still runs fine as expected.

Any other suggession pl?

Regards,


Regards,
Spareus
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-17 : 13:39:58
Sure, you need to fix you assignment statement:
select @ctx = min(ctx) from [TMPSR] where ctx > @ctx
If the select doesn't return any rows, then @ctx remains unchanged, thus the endless loop. It should be something like:
SET @ctx = (select min(ctx) from [TMPSR] where ctx > @ctx)


That's one of several reasons why I always advise agaisnt doing assignment in the select statement.
Go to Top of Page

spareus
Yak Posting Veteran

52 Posts

Posted - 2013-06-18 : 03:43:57
Hi Lamprey,
Thanks. I will try it out and let you know.
I am not sure I understand differance between select and set.
I am fairly new in SQL.
I will google out and which one is preferable in what conditions.

Thanks again for help.
I will let you know the results.

Regards,


Regards,
Spareus
Go to Top of Page
   

- Advertisement -