November 1, 2013 at 7:29 am
I am converting an old DTS package. I need to process through a temp table i create using a while statement(end of table counter). I set certain parameters. and then use IF logic email to different types of emails...there could be a blank email address, a bad email address, or a good email address that is successful.
the basic logic is:
DELETE FROM msdb.dbo.sysmail_mailitems
while (@count <= @Tablerows)
begin
set @Recipient_Email =
set @Body1 =
IF (SELECT min(sent_status) FROM msdb.dbo.sysmail_mailitems) is null
this means this is the first pass and i send a generate email in order to populate sysmail_mailitems with
a good record. and i don't increment my row counter - get same record from temp table.
IF @Recipient_Email is null
id the temp table does have a email address i send an email to the person who created the record
set up email, send email, increment my row counter - get next record from temp table
IF @Recipient_Email IS NOT NULL
That means there is an email address...but it may not be a properly formatted email, so it may fail.
I set up the email, send the email. set a delay: WAITFOR DELAY '00:00:10' ---- 10 Second Delay
i need to put logic here to check whether the email was successful:
IF ((SELECT min(sent_status) FROM msdb.dbo.sysmail_mailitems) <> 1)
it failed and i set up email to notify the person the created the record to fix it.
increment row counter, get next next record from temp table
ELSE
it was sent successfully
increment row counter, get next record.
END
END
I cannot get this logic structure to work properly....
Can I NOT use nested if logic like this in a do-while-loop?
when i do it without the nested if statement, it works, but provides strange results and i think it is because sysmail_itmes
hasn't been updated so i really interrogating the wrong record
Also: is it smart logic to interrogate sysmail_items as some other process may insert a row in there?
any suggestions to my approach would be appreciated.
November 1, 2013 at 11:02 am
Stepping through this is see that it is evaluating each IF statement during each iteration...not what i wanted..
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply