SQL Job - almost good

Question

SQL Job - almost good

Bron Tamulis

Ten Centuries

Points: 1090
More actions
October 25, 2016 at 5:07 pm

#314757

Okay - I need to accomplish the following:
1. Read SQL Table - if query returns results then send email with query results
2. After sending email - update the table by changing a value (so we don't re-send this record)
I have a job with two steps.
Step one sends the email -
EXEC [msdb]..sp_send_dbmail
@profile_name='Default',
@recipients='XXXXX@hotmail.com',
@subject='DB Mail',
@body='Bryan this is the sample email for Worth MOP to SOP Changes',
@execute_query_database = 'WORTH',
@query = 'exec X_MOPSOPLINK',
@attach_query_result_as_file=1,
@query_attachment_filename = 'SOP to MOP Changes.txt'
This works great except that it sends an email regardless if the query has results - is there an easy way to modify so that it only sends email if there is a query result?
@query fires this stored procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE
[dbo].[X_MOPSOPLINK]
AS
BEGIN
SELECT CUSTID, CUSTNAME, MANUFACTUREORDER_I, SOPNUMBE, ITEMNMBR, ITEMDESC,
Convert(date, NEW_ENDDATE) as NEWDATE,
Convert(date, OLD_ENDDATE) as OLDDATE
-------, OLD_SOPREQSHIPDATE, NEW_SOPREQSHIPDATE
FROM X_SOPMOP_Link_2
where NOTICE_SENT=0
END
GO
I'm wondering if I should try to modify the stored procedure to include the send email procedure? If so, what is best method? Any suggestions would be appreciated........

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Bron Tamulis (10/25/2016)
Okay - I need to accomplish the following:
1. Read SQL Table - if query returns results then send email with query results
2. After sending email - update the table by changing a value (so we don't re-send this record)
I have a job with two steps.
Step one sends the email -
This works great except that it sends an email regardless if the query has results - is there an easy way to modify so that it only sends email if there is a query result?

I'm wondering if I should try to modify the stored procedure to include the send email procedure? If so, what is best method? Any suggestions would be appreciated........

One approach is to do as you mention and have the email call in the stored procedure. It looks like from your query, you need to work with all rows where no notice has been sent. You can just get the count for the table where NOTICE_SENT=0, if that count is greater than 0, then do the EXEC [msdb]..sp_send_dbmail ......

You could also send an email to just yourself or whoever if you need to know that nothing was sent due to no data. If count > 0 then send mail with query else send mail to DBA. But that could be obnoxious depending on the business, data, how often and when this is executed. Just a thought.

Sue

Lowell SSC Guru Points: 323518 More actions · Answer 2

like Sue_H, i would have the procedure send the email. if you are reusing the procedure elsewhere, add an optiona parameter with a default, like @sendEmailNotification int=0

it looks like a simple IF EXISTS would work; something like this:

IF EXISTS(SELECT 1 FROM X_SOPMOP_Link_2 where NOTICE_SENT=0)

AND @sendEmailNotification >0

BEGIN

--the exact same send email code goes here.

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bron Tamulis Ten Centuries Points: 1090 More actions · Answer 3

Thank you both - Sue and Lowell..........

Quick follow up question - my step 2 is to update the table by setting the NOTICE_SENT column = '1' - is a simple update statement best or should I create a separate stored procedure that I fire on step 2 - updating the column?

Any thoughts or best practices?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

If I were doing this, I would fire the email and update the table in the same procedure. I don't see a reason to separate them, unless one activity can be done without the other, but it doesn't sound like that's the case here.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Ed Wagner (10/26/2016)
If I were doing this, I would fire the email and update the table in the same procedure. I don't see a reason to separate them, unless one activity can be done without the other, but it doesn't sound like that's the case here.

There's a very good reason. Copy your DB or even just the proc from Prod to Dev to refresh the Dev environment and watch someone inadvertently send an email to the customers with totally whacked data or some choice remarks that developers are sometimes known to put into their tests.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 6

But thats easily fixed with smart code

If exists... and @@SERVERNAME='Production' And db_name='ProdDB'

So i would argue it all belongs in a single procedure.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!