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,633 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
Comment Utility
You need to select from that function,

 @query = select * from func_return_somethinx()'
0
 

Author Comment

by:5c75f63a
Comment Utility
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
Comment Utility
You need to pass each value to sp_send_dbmail procedure.

Not a result of Table valued function.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 2

Expert Comment

by:Pratik Makwana
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now