IF EXISTS (EXEC..... )
Posted on 2006-11-03
I have a proc which checks trade data for suspect/invalid data; like <0 prices, invalid symbols, <0 quantities...things of this nature.
now, if any suspect data is found, the proc returns it like this:
Suspect Trades - UserIDs:
6 identifying fields of the trades found with suspect userids....
Suspect Trades - Symbols:
6 identifying fields of the trades found w/suspect symbols....
Suspect Trades - Quantity:
6 identifying fields of the trades found w/suspect quantities...
See...if suspect data is found, it is returned for the user to review. If not, nothing is returnred. There are 7 items checked at each invocation. If any of the seven are found suspect, an email is sent.
All this is fine right now, i created the proc, then created suspect data, they're detected and the notice is sent just fine. This is done on a scheduled basis, every 5 minutes, everything works just fine. BUT, I just now deployed it to the prod box, ran it, and found it to come back clean, as I had forgotten to make some suspect data, so everything was good, and i got a blank email
If nothing is found suspect, I'd really like to refrain from sending an email. So, I thought I'd do it like this:
IF EXISTS (exec proc....)
update a flag value which ensures the email is sent IF it is updated to a certain value
but, apparently i can't do that with the EXEC, or my syntax is just bad.
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'EXEC'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.
i really don't want to send blank emails...these are alerts....they should only be sent if/when a value is found to be suspect
can anybody provide some insight? please