@@ROWCOUNT ISSUE

Question

@@ROWCOUNT ISSUE

BeginnerBug

Hall of Fame

Points: 3539
More actions
October 16, 2010 at 7:01 am

#145648

insert x values(a)
if @@rowcount>0
update x set col=2
if @@rowcount>0
print 'success'
in above sample... if update fails is there any possibility tht @@rowcount take insert stmt value and print success..?
in tht case how can i make sure tht second @@rowcount takes only from update stmt?.
is it good to SET @@rowcount=0 after first insert?...
Pls help on this.... thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

@@RowCount ALWAYS takes the row count of the last statement executed. For the second @@rowcount, that will be either the update (if it runs) or the IF.

What do you want that second @@rowcount to reflect the row count of? The insert? The Update if it happens and 0 otherwise?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 2

GilaMonster (10/16/2010)
@@RowCount ALWAYS takes the row count of the last statement executed. For the second @@rowcount, that will be either the update (if it runs) or the IF.
What do you want that second @@rowcount to reflect the row count of? The insert? The Update if it happens and 0 otherwise?

Hi...

i want that second @@rowcount to reflect the row count of the update if it happens and 0 otherwise.....

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That's exactly what it will do as written.

If you want to make it clearer...

DECLARE @RowsUpdated INT

SET @RowsUpdated = 0

INSERT x VALUES (a)

IF @@rowcount>0

BEGIN

UPDATE x

SET col=2

SET @RowsUpdated = @@RowCount

END

IF @RowsUpdated>0

PRINT 'success'

p.s. An insert with values will always have a rowcount > 0 unless there was an error. For errors you should be using try ... catch

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 4

BeginnerBug

Hall of Fame

Points: 3539

October 16, 2010 at 8:43 am

#1238018

Thanks a lot Gail......