Raiserror in stored Proc that will notify users when records exist

I have been tasked with  creating a stored procedure in Sql Server that if the query brings back a value when executed at a specific time by a Sql Job a custom error will go out to specific operators.  Another Requirement is that i have to return the query in the email so that the operator can research the records.  If the query is executed and does not bring back any records then it ends with no notification.  Below is a query that i wrote that brings back an error if i run it but when i run it in a job the test notification does not send and I have yet to figure out how to return the query that i ran.

				Count(distinct id),
					    FROM [TestTable] ttbl (nolock)
        Where orderdate> getdate() - 2 
	    group by orderdate
having count(distinct id) > 1
	RAISERROR(50031, 10, 0) 

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vadim RappCommented:
Probably there's no need in raiserror, simply send email using xp_sendmail or sp_send_dbmail, depending on the version of sql server. Best is to send to mail alias and include in the alias the individuals you want. Also, consider putting that not in sql job but in the trigger on the table you look at, then it will be instant notification.
Well, I think you can leave in the job just like you are doing.

However, first of all, I don't see the message that goes out when a certain condition occurs.

I see numbers. Maybe ,you are translating the numbers to messages somewhere.

In any case, the way I have done these things in the past but my issue is that your requirements aren't very clear.

In my case, I will grab records that meet or does not meet certain conditions and store them in another table.

I called mine notifications table.

In this table, I have a flag called sent set to No by default.

All instances that meet or does not meet your requirements gets stored on this table.

When  a notification is sent, this flag is set to Yes so it doesn't get sent again until another record is stored there.
Vadim RappCommented:
@sammySeltzer, I think what he expects, is this: he created an Agent job and configured it to send alert on job failure. If condition is met, he wants the job to fail.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ok, I see my bad. I read it incorrectly.
RiveraJdataAuthor Commented:
Something i did not add was that i added a custom message in the sql alerts table above 50000.  I guess what i would like to know is How do i Return the query that i executed when executed and it meets the criteria and errors out.  also I created a test error and placed it in my test environment and setup sql mail with no success. Thanks for everything
RiveraJdataAuthor Commented:
I was able to figure out a solution by creating a second stored proc that on failure of the Job it would execute the and query the MSDB database to get the query and the error that it bounces back and uses the calls the dbsendmail proc.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.