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

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.
5c75f63aAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
You need to select from that function,

 @query = select * from func_return_somethinx()'
0
5c75f63aAuthor Commented:
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
Alpesh PatelAssistant ConsultantCommented:
You need to pass each value to sp_send_dbmail procedure.

Not a result of Table valued function.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Pratik MakwanaData AnalystCommented:
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
ZberteocCommented:
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
Scott PletcherSenior DBACommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.