April 1, 2016 at 12:37 pm
I would like to check if the results for a select query is null. If there are no rows returned print "no rows" else send an email.
What is the preferred way to script this.
Is there a better way to accomplish this other than this?
IF EXISTS(SELECT .... FROM Table1 where ....)
BEGIN
msdb.dbo.sp_send_dbmail....
END
ELSE
PRINT 'No Rows'
Many thanks.
April 1, 2016 at 1:06 pm
SQL!$@w$0ME (4/1/2016)
I would like to check if the results for a select query is null. If there are no rows returned print "no rows" else send an email.What is the preferred way to script this.
Is there a better way to accomplish this other than this?
IF EXISTS(SELECT .... FROM Table1 where ....)
BEGIN
msdb.dbo.sp_send_dbmail....
END
ELSE
PRINT 'No Rows'
Many thanks.
This is how I would do it.
April 1, 2016 at 2:51 pm
Thanks Phil.
April 2, 2016 at 9:37 am
Just be aware that by using this method, the query has to be evaluated twice. Often not a big problem, but if it's a query that takes a lot of resources, you might want to consider using a helper table to store the results. In that case, the rought structure would be:
SELECT ...
INTO dbo.HelperTable
FROM ...
WHERE ...;
IF @@ROWCOUNT > 0
BEGIN;
EXEC msdb.dbo.sp_send_dbmail....;
END;
ELSE
PRINT 'No Rows';
(And then the sp_send_dbmail would use SELECT list, the, columns FROM HelperTable)
Note that I do not use a temporary table here. The SELECT will run in a different scope, started by a service broker process, so it has to be a permanent table. Also, do not drop the table at the end of the process. The mail process is asynchronous so it's probably not yet finished when you drop the table.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply