How can i pass multiple query in a @query parameter in sp_send_dbmail?
For example:
select count(*) from TableA
IF count(*)/ @@rowcount = 0
Exec sp_send_dbmail @profile_name = xx, @recipients = '[email protected]',@subject = 'Test', @body= ' No rows';
IF count(*)/ @@rowcount > 0
Exec sp_send_dbmail @profile_name = xx, @recipients = '[email protected]',@subject = 'Test',
@body= ' xx rows';
I am not getting any error message, but it stops after the first select statement.
When you do the select count(*) from TableA you do not keep the value somewhere, it only execute and return the result from your query. This why the if statements are not firing. You could do something like this:
DECLARE @c AS int
SET @c = (select count(*) from TableA)
IF @c = 0
Exec sp_send_dbmail @profile_name = xx, @recipients = '[email protected]',@subject = 'Test', @body= 'No rows';
IF @c > 0
Exec sp_send_dbmail @profile_name = xx, @recipients = '[email protected]',@subject = 'Test',
@body= ' xx rows';
Though I have some reservations about putting this kind of logic on the SQL side. But since I don't know how you are using this, it should work for now.
