question

Hi I have cobbled together a select statement from around the web, which selects data and emails the results as a HTLM formatted table. This works really well and the results are readable, unlike an unformatted text output. However if the select doesn't finds any results, it sends a blank email, which is sub-optimal. there are lots of examples around of this formatting, but I can't find any references to returning no rows. I have tried doing a check on @@ROWCOUNT but it seems to be ignored. So my question is, how do I check the result of the query or the content of the @tableHTML, to change the body of the email to read something along the lines of, 'No results were found today' if 0 rows are selected. Sql Server 2012 DECLARE @bodyMsg nvarchar(max) DECLARE @subject nvarchar(max) DECLARE @tableHTML nvarchar(max) SET @subject = 'Speedy Candidates Incorrectly Validated' SET @tableHTML = N' '+ N' ' + CAST ( ( SELECT td = u.[USERNAME], '', td = u.[FORENAME], '', td = u.[SURNAME], '', td = c.[TELEPHONE], '', td = b.[DAY_OF_BOOKING], '', td = b.[STATE], '', td = u.[REALM], '', td = b.[BOOKING_REFERENCE], '', td = p.[PaymentID], '', td = p.[State], '', td = sm.[Reference] from [GENE].[dbo].[BOOKING] b join [GENE].[dbo].[CANDY] c on c.id = b.CANDY join [GENE].[dbo].[USERS] u on u.ID = c.USERS_ID join [payco].[dbo].Payment p on p.OrderId = b.BOOKING_REFERENCE join [payco].[dbo].PaymentEvent sm on sm.PaymentID = p.PaymentID where b.CANCELLATION_REASON = 'CANCELLED_WHEN_IS_INELIGIBLE' and u.REALM = 'SPEEDY' and cast(b.CREATED_DATE As date) > cast(getdate()-1 as date) order by 1 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N' ' + N' UserNameForenameSurnameTelephoneDayOfBookingStateRealmBookingReferencePaymentIDPaymentStateReference ' "" -- non breaking hyphen ‑ Set @tableHTML = Replace(@tableHTML, '-', '‑') -- no data? --if (@@ROWCOUNT =0) --begin -- select 'no wows detected' --END EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLAlerts', @ recipients='me@here.com', @subject = @subject, @body = @tableHTML, @append_query_error = 1, @body_format = 'HTML'