Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-05-13
6
Medium Priority
?
294 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

704 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