Reputation: 1041
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.
Upvotes: 0
Views: 257
Reputation: 246
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.
Upvotes: 1