• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

Why does this use of rowcount and return, not return?

Hi,
I am using SQL server 2000 and exec to send email.
I have a trigger which begins by checking the rowcount affected of the last executed statement.  If it is 0, it is supposed to abort, however this didn't seem to be happening and when I introduced the next line shown which emailed me if rowcount was 0, I was suprised to find it fired every time even though from the way I read this, it wouldn't get to this line.
Why is it that two tests of ROWCOUNT following each other would produce different actions?
Thanks,
IF @@ROWCOUNT = 0  RETURN
if (@@ROWCOUNT = 0) exec master..xp_sendmail @recipients = 'test@test.com',@message = 'message',@subject = 'subject'

Open in new window

0
dgloveruk
Asked:
dgloveruk
  • 4
1 Solution
 
RiteshShahCommented:
@@ROWCOUNT will give you results right after your statement execute, after than its value going to be flush, so if you want to use value, you have to preserve it in variable like code given below.




declare @rowCount
---your code and execution, as soon as your execution completes use below code
set @rowcount=@@rowcount
IF @rowcount = 0  RETURN
if (@rowcount = 0) exec master..xp_sendmail @recipients = 'test@test.com',@message = 'message',@subject = 'subject'

0
 
RiteshShahCommented:
forgot to give datatype to @RowCount variable. here find corrected version of code.

declare @rowCount int
---your code and execution, as soon as your execution completes use below code
set @rowcount=@@rowcount
IF @rowcount = 0  RETURN
if (@rowcount = 0) exec master..xp_sendmail @recipients = 'test@test.com',@message = 'message',@subject = 'subject'

0
 
RiteshShahCommented:
moreover, one your execute RETURN statement if is going to return, not going to look at the code below.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Muhammad Ousama GhazaliSolution Analyst & ArchitectCommented:
RiteshShah has already given the approriate answer. However, I was wondering if it will will ever execute the second statement because both have the same condition test and if the first test is successful then the execution will return and email sending statement will never be executed. So is there a mistake in the second statement's if which should be ELSE IF or I am missing something to understand it correctly.
0
 
RiteshShahCommented:
in short, you need to understand quite a few things.

-- once you RETURN, not going to look at anything else below it.
-- @@ROWCOUNT flushes its value, if you need to use it for later use, store it in variable,
--why you need same two IF with same condition, can't you use something like below?

IF @@ROWCOUNT = 0  
BEGIN
exec master..xp_sendmail @recipients = 'test@test.com',@message = 'message',@subject = 'subject'
RETURN
END


above script will first send an email than RETURN, won't it be more readable?
0
 
dgloverukAuthor Commented:
Hi there,
@@ROWCOUNT flushes its value, that answers the question.
The lines I presented weren't the functional objective of the procedure, they were simply what I came to in determining where the problem lay in my understanding of how the lines should have behaved.
Thank you so much for your reply,
Regards
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now