Solved

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

Posted on 2009-05-13
6
255 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
  • 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

939 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

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now