Scope of @@rowcount

• BeginnerBug

  Hall of Fame

  Points: 3539

  More actions

  October 12, 2010 at 2:09 am

  #143642

  whts the scope of @@rowcount

  for ex

  begin

  insert stmt

  if @@rowcount>0

  begin

  insert stmt

  if @@rowcount>0-- will it be affected by the first insert statement?

  do smthg

  end

  end

  any help?

• tommyh

  SSCertifiable

  Points: 6302

  More actions

  October 12, 2010 at 2:12 am

  #1235336

  Yes. As in Yes it will get effected.

  /T

• BeginnerBug

  Hall of Fame

  Points: 3539

  More actions

  October 12, 2010 at 5:26 am

  #1235405

  but that shouldnt affect from first @@rowcount value..right?.. is thr any other way to make sure a rows are inserted or updated?

• Lowell

  SSC Guru

  Points: 323522

  More actions

  October 12, 2010 at 5:32 am

  #1235408

  every insert/update/select/set/delete statement resets the @@rowcount to the rows affected by the executed statement, so it's a best practice to use a variable to capture the rowcount you need,:

  begin

  declare @myrowcount int,

  @myrowcount2 int

  insert stmt

  SET @myrowcount=@@rowcount

  if @myrowcount>0

  begin

  insert stmt

  SET @myrowcount2 =@@rowcount

  if @myrowcount2 >0-- will it be affected by the first insert statement?

  do smthg

  end

  end

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• BeginnerBug

  Hall of Fame

  Points: 3539

  More actions

  October 12, 2010 at 5:55 am

  #1235428

  Thanks Lowell!!!!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 12, 2010 at 6:28 am

  #1235448

  Lowell (10/12/2010)

  ---

  every insert/update/select/set/delete statement resets the @@rowcount to the rows affected by the executed statement

  It's more general than that. It's just about every statement.

  SELECT * FROM master.sys.objects -- 74 rows

  IF (1=1)

  SELECT @@ROWCOUNT AS RowsAffected -- 0, because the IF did not affect any rows

  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

• Lowell

  SSC Guru

  Points: 323522

  More actions

  October 12, 2010 at 6:35 am

  #1235452

  GilaMonster (10/12/2010)

  ---

  It's more general than that. It's just about every statement.

  SELECT * FROM master.sys.objects -- 74 rows

  IF (1=1)

  SELECT @@ROWCOUNT AS RowsAffected -- 0, because the IF did not affect any rows

  wow i did not know that even an IF statement affects it! thanks once again Gail!

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 12, 2010 at 6:59 am

  #1235478

  Lowell (10/12/2010)

  ---

  GilaMonster (10/12/2010)

  ---

  It's more general than that. It's just about every statement.

  SELECT * FROM master.sys.objects -- 74 rows

  IF (1=1)

  SELECT @@ROWCOUNT AS RowsAffected -- 0, because the IF did not affect any rows

  wow i did not know that even an IF statement affects it! thanks once again Gail!

  I found that out after using this construct and getting really, really confused...

  Insert <stuff>

  IF @@Error = 0

  SET @RowCount = @@RowCount

  ELSE

  GOTO ErrorHandler

  @@Error behaves the same way.

  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