@@ROWCOUNT Not Accurate?

1

hi experts, I want to only send the email If Rows Are Returned. But the report is being sent even if row count is zero for some reason.
ANy ideas? Thanks

DECLARE @currDate Date = GETDATE();
DECLARE @30DaysFromNow Date;
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
Set @30DaysFromNow = DateAdd(Day, +30, @currDate)

SET @xml = CAST((SELECT dbo_Orgs_1.Name AS 'td', '', Orgs.Name AS 'td', '', ViewCWTContracts.EffectiveStartDT AS 'td', '', ViewCWTContracts.EffectiveEndDT AS 'td', ''

FROM Orgs INNER JOIN (ViewCWTContracts INNER JOIN Orgs AS dbo_Orgs_1 ON ViewCWTContracts.CarrierOrgId = dbo_Orgs_1.Id) ON Orgs.Id = ViewCWTContracts.ParentOrgId
GROUP BY dbo_Orgs_1.Name, Orgs.Name, ViewCWTContracts.EffectiveStartDT, ViewCWTContracts.EffectiveEndDT
HAVING (((ViewCWTContracts.EffectiveEndDT) < @30DaysFromNow And (ViewCWTContracts.EffectiveEndDT) > @currDate))
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX));

If @@ROWCOUNT > 0

SET @body ='

TMS 2 email sarah cwt contracts ending in 30 days

'

SET @body = @body + @xml +'

Name1 Name2 StartDT EndDT
'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'MyProfile',

@body = @body,

@body_format ='HTML',

@recipients = 'my email;',

@subject = 'subject';

2

I believe the @@ROWCOUNT is for the SET statement, NOT for the SELECT.

Also, you're missing a BEGIN / END after the IF, so the "EXEC ... dbmail ..." will always run.

Instead of using @@ROWCOUNT, I suggest checking the length of the XML:


IF LEN(@xml) >= 10 /*or whatever length you choose*/
BEGIN
    SET ...
    EXEC ...
END /*IF*/
1 Like
3

@ScottPletcher checking the length of the XML is working well. Thanks.

4

There were a couple of posts over on SQLServerCentral where I posted some templates for generating emails. I know one of them is a bit long - but there are some good examples in that post: