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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 16, 2010 at 7:49 am

  #1238006

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

  October 16, 2010 at 8:12 am

  #1238010

  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

  October 16, 2010 at 8:39 am

  #1238017

  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

  October 16, 2010 at 8:43 am

  #1238018

  Thanks a lot Gail......