SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 PRINT statement changes execution
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

spareus
Yak Posting Veteran

India
52 Posts

Posted - 06/14/2013 :  10:58:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 06/14/2013 :  12:54:05  Show Profile  Reply with Quote
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

India
52 Posts

Posted - 06/17/2013 :  04:44:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 06/17/2013 :  13:39:58  Show Profile  Reply with Quote
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

India
52 Posts

Posted - 06/18/2013 :  03:43:57  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000