Solved

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

Posted on 2009-05-13
6
287 Views
Last Modified: 2012-05-06
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
Comment
Question by:dgloveruk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24373352
@@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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24373363
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24373455
moreover, one your execute RETURN statement if is going to return, not going to look at the code below.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24373466
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
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24373502
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
 

Author Comment

by:dgloveruk
ID: 24373539
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question