What's wrong?

Transact-SQL

Rudemusik


Hi All!

Simple problem, I'm missing something.

I have this at the end of my statement

IF @@ROWCOUNT = 0

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'jrudolf@ikon.com',

@body= @body,

@body_format = 'HTML',

@Subject='NO RECORDS TO DELETE',

@profile_name = 'Ikon'

Else

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'jrudolf@ikon.com',

@body= @body,

@body_format = 'HTML',

@Subject='PLEASE REVIEW RECORDS',

@profile_name = 'Ikon'

END

I'm thinking when it returns 0 rows it should do the first email, else it should do the second email. But it's always doing the second. And I know it's 0 rows, because I'm not getting any results. And I made sure of it.

Thanks!

Rudy

Top

Re: What's wrong?

Anthony Martin


What is the statement right before @@rowcount

Try doing a print of @@rowcount to make sure it is 0






Top

Re: What's wrong?

richbrownesq

Whats the statement just before the IF statement

Remember, @@ROWCOUNT gives you the number of rows affected by the last processed statement within the batch. Its good practice to always set the value of @@ROWCOUNT to a variable if you're going to use the value later on in a stored procedure.






Top

Re: What's wrong?

Rudemusik

Hello!

I wasn't sure if this was needed. But here is the whole statement!

DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)

BEGIN

SET @xml =CAST(( SELECT DISTINCT count (*) AS 'td', '', First_Name As 'td', '', (Service_Date_Time) As 'td'

FROM [Pharm Test Local].dbo.Active_Orders WHERE (Service_Date_Time <= dateadd( Month, -36, getdate())) Group By Service_Date_Time, First_Name FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

+CAST(( SELECT count (*) AS 'td', '', First_Name As 'td', '', (Service_Date_Time) As 'td'

FROM [Pharm Test Local].dbo.Archive_Orders WHERE (Service_Date_Time <= dateadd( Month, -39, getdate())) Group By Service_Date_Time, First_Name FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><H1>Records that have expired.</H1><body bgcolor=White><table border = 2><tr><th># of Records</th> <th>DocType</th> <th>Scan Date</th></tr>' SET @body = @body + @xml +'</table></body></html>'

IF @@ROWCOUNT = 0

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'jrudolf@ikon.com',

@body= @body,

@body_format = 'HTML',

@Subject='NO RECORDS TO DELETE',

@profile_name = 'Ikon'

Else

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'jrudolf@ikon.com',

@body= @body,

@body_format = 'HTML',

@Subject='PLEASE REVIEW RECORDS',

@profile_name = 'Ikon'

END

Thanks!

Rudy


Top

Re: What's wrong?

Rudemusik

Ok.

I took Anthony advice and did a PRINT. It alwyas comes up as 1. So now I'm confused. I thought it would show how many rows are returned. My reults are returning 7 rows, or when I up the number of months to 67 or something, I get no results. I thought that would return a 0. So how can I set this up so I can send one email when there is a results, and the other when there isn't.

Thanks!

Rudy


Top

Re: What's wrong?

richbrownesq

As suggested, its because the statement directly before your IF block will be setting @@ROWCOUNT =1

Try:

Code Snippet

DECLARE @Body VARCHAR(MAX)

SET @Body = 'All your HTML stufff here'


SELECT @@ROWCOUNT

It should = 1

I'd suggest doing a different test to see if you have any rows to delete, perhaps based upon an xml query or do your row test early in the proc

eg (Pseudo Code)

Code Snippet

DECLARE @RCount INT


SET @RCount = (SELECT COUNT(*) FROM YourTable WHERE SearchCondition = @True)


IF @Rcount = 0

sp_send_mail1

ELSE

sp_send_mail2

HTH!





Top

Re: What's wrong?

Hunt Tsai

Hi Rudy,

as the whole statement, it seems like "correct".

but u did this " set @xml = ( select distinct ....) "

and varchar(max) will integrate the reuslt into one column.

that's why u always get 1.

if select statement got nothing , and @@rowcount will be 0.

put a line " and 1=0 " into select statement.

it'll make it be ZERO.

Try it, and make sure your statement and logic will make a situation with "Get NOTHING "

Best Regrades,

Hunt.





Top

Re: What's wrong?

Manivannan.D.Sekaran

In your case, you can't use @@ROWCOUNT.

Why

Check the below example - The @@ROWCOUNT will be updated by the SET statement; not by the subquery; So you always get @@ROWCOUNT as 1; (the subquery always return either some valid value or NULL).

Code Snippet

Declare @XML as xml

Set @XML = (select id from sysobjects where id=0 for xml auto)

Select @@Rowcount


So, Check the XML content is blank or NOT;

Code Snippet

Declare @rowcount as int;

SET @xml = Isnull(CAST((SELECT DISTINCT count (*) AS 'td', '', First_Name As 'td', '', (Service_Date_Time) As 'td'

FROM [Pharm Test Local].dbo.Active_Orders

WHERE (Service_Date_Time <= dateadd( Month, -36, getdate()))

Group By Service_Date_Time, First_Name FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

,'')

+

Isnull(CAST((SELECT count (*) AS 'td', '', First_Name As 'td', '', (Service_Date_Time) As 'td'

FROM [Pharm Test Local].dbo.Archive_Orders

WHERE (Service_Date_Time <= dateadd( Month, -39, getdate()))

Group By Service_Date_Time, First_Name FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

,'')

SET @body ='<html><H1>Records that have expired.</H1><body bgcolor=White><table border = 2><tr><th># of Records</th> <th>DocType</th> <th>Scan Date</th></tr>'

SET @body = @body + @xml +'</table></body></html>'

IF @xml = ''

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'jrudolf@ikon.com',

@body= @body,

@body_format = 'HTML',

@Subject='NO RECORDS TO DELETE',

@profile_name = 'Ikon'

Else

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'jrudolf@ikon.com',

@body= @body,

@body_format = 'HTML',

@Subject='PLEASE REVIEW RECORDS',

@profile_name = 'Ikon'

END





Top

Re: What's wrong?

Rudemusik

Perfect Mani. Nice work around.

Thanks!

Rudy


Top