Solved

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

Posted on 2009-05-13
6
273 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

808 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