Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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
Medium Priority
?
2,267 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 2000 total points
ID: 40302845
You need to pass each value to sp_send_dbmail procedure.

Not a result of Table valued function.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 27

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 70

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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard 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.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

581 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