Solved

How to send email only if query returns > 0 rows with sp_send_dbmail in SQL Server 2008

Posted on 2014-09-03
6
1,792 Views
Last Modified: 2014-09-07
Environment is SQL Server 2008:

The objective is to perform a query and send an email only if the query returns 1 or more rows.  The query is quite complex and needs to be maintained in a single place only (i.e. a function).  I have created a stored procedure as follows.

CREATE PROCEDURE sp_do_somethinx AS BEGIN
DECLARE @temp_table TABLE ( ... );
DECLARE @cnt int;

INSERT INTO @temp_table EXEC func_return_somethinx();
SELECT @cnt = COUNT(*) FROM @temp_table;

IF @cnt > 0
BEGIN
  EXEC msdb.dbo.sp_send_dbmail ( @query = 'exec func_return_somethinx()' )
END
END

EXEC sp_do_somethinx yields:
Error: The request for procedure 'y' failed because 'y' is a table valued function object.

Now obvoiusly sp_send_dbmail operates in its own context independent from the procedure above, but the result from func_return_somethinx does not depend on the the state of sp_do_somethinx.    The docs [1] says @query "can contain any valid Transact-SQL statements".

How can I send email with sp_send_dbmail only if the function's query returns > 0 rows?

Note that the sp_send_dbmail will work when @query = 'exec sp_somequery', but that prohibits the use of the INSERT INTO @temp_table EXEC statement.

[1] http://msdn.microsoft.com/en-us/library/ms190307.aspx

Please advise. Thank you.
0
Comment
Question by:5c75f63a
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40302325
You need to select from that function,

 @query = select * from func_return_somethinx()'
0
 

Author Comment

by:5c75f63a
ID: 40302801
Thank you for the suggestion.  Unfortunately, the error message remains the same:
IF @cnt > 0
BEGIN
  EXEC msdb.dbo.sp_send_dbmail 
    @query = 'SELECT * FROM func_return_somethinx()';
END
exec sp_do_somethinx
The request for procedure 'func_return_somethinx()' failed because 'func_return_somethinx()' is a table valued function object.

Open in new window

Is a function call not supported in the argument to sp_send_dbmail?
0
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 500 total points
ID: 40302845
You need to pass each value to sp_send_dbmail procedure.

Not a result of Table valued function.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 2

Expert Comment

by:Pratik Makwana
ID: 40303356
if exists (select * from tempdb..sysobjects where name = 'TESTRecords' and Xtype = 'U')
drop table tempdb..[TESTRecords]
GO

Declare @SQL varchar(4000)
Set @SQL=''

Set @SQL='Select distinct Category,Brand,Comments1,ISNULL(ProductTerritorySubGroup,'''') as [Segment] from DATABASE.DBO.TestDetailReports with(nolock)
Where Category like ''%Fabric Softener%''
Order by 1,2'
Exec(@SQL)

IF @@rowcount = 0
      Begin
                  Select distinct Category,Brand,Comments1,ProductTerritorySubGroup as [Segment]
                  INTO TempDB.dbo.TESTRecords
                  from DATABASE.DBO.TESTDetailReports with(nolock) where 1=2

                  Insert Into tempdb..TESTRecords (Category) Values ('New record not exist')
      End
Else
      Begin
                  Select distinct Category,Brand,Comments1,ISNULL(ProductTerritorySubGroup,'') as [Segment]
                  INTO TempDB.dbo.TESTRecords
                  from DATABASE.DBO.TestDetailReports with(nolock)
                  Where Category like '%Fabric Softener%'
                  Order by 1,2
      End


Try this and then execute your email......
I copy table data into local temp table and then if data is exist or not it send me particular result.
Like.. if data is there then it comes with data in excel and if not then it comes with blank excel with message.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40303794
Why don't you put the result in a temporary permanent table table and use that to send email. You could do:
if object_id('database.owner.tmp_send_email_table_7456')
	drop table owner.tmp_send_email_table_7456
select * into tmp_send_email_table_7456 from func_return_somethinx()
EXEC msdb.dbo.sp_send_dbmail 
    @query = 'SELECT * FROM tmp_send_email_table_7456'

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40304006
This is what's causing the error:
INSERT INTO @temp_table EXEC func_return_somethinx();
You cannot load a table variable using EXEC.


CREATE PROCEDURE sp_do_somethinx
AS
SET NOCOUNT ON;
DECLARE @rowcount int

CREATE TABLE tempdb.dbo.sp_do_somethinx_work_table (
    ... )

INSERT INTO tempdb.dbo.sp_do_somethinx_work_table
EXEC func_return_somethinx()
SET @rowcount = @@ROWCOUNT

IF @rowcount > 0
BEGIN
    EXEC msdb.dbo.sp_send_dbmail ..., @query = 'SELECT * FROM tempdb.dbo.sp_do_somethinx_work_table', ...
END --IF
GO --end of proc
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 37
Separate 2 comma delimited columns into separate rows 2 41
Are triggers slow? 7 14
SQL Recursion 6 20
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question