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
EXEC msdb.dbo.sp_send_dbmail ( @query = 'exec func_return_somethinx()' )
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  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.
Please advise. Thank you.