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,724 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
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.

 
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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

785 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