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.
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 cpcomDECLARE @date DATEdeclare @mailcount intdeclare @recipients nvarchar(max)declare @ctx nvarchar(255)declare @subject nvarchar(255)--declare @date dateset @date='2013-06-12'set @mailcount=0if @date is null OR @DATE=''beginset @date = getdate() endprint @datetruncate table tmpsrinsert into tmpsrselect * from srcp where @date = dateselect @ctx = min(ctx) from TMPSR SET @subject = 'Your billing details - ' + (FORMAT(@date, 'dd MMM yyyy'))while @ctx is not nullbeginDECLARE @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 nullprint @recipients -- all mails sent but remove this line and query hangs by repeating same outputbeginEXEC msdb.dbo.sp_send_dbmail @recipients=@recipients, @subject = @subject, @body = @tableHTML, @blind_copy_recipients = 'abc@def.com', @body_format = 'HTML' ;UPDATE SRCPSET 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 > @ctxset @mailcount= @mailcount + 1endENDELSEBEGINPRINT 'ctx ' + @ctx + ' NOT FOUND IN MAILD ID'select @ctx = min(ctx) from [TMPSR] where ctx > @ctxENDend--ENDprint '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 nullprint @recipients -- all mails sent but remove this line and query hangs by repeating same outputbegin..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. |
|
|
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 |
|
|
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. |
|
|
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 |
|
|
|
|
|
|
|