|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Wednesday, October 31, 2012 1:16 AM
Points: 127,
Visits: 348
|
|
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?
|
|
|
|
|
UDP Broadcaster
      
Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476,
Visits: 1,943
|
|
Yes. As in Yes it will get effected.
/T
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Wednesday, October 31, 2012 1:16 AM
Points: 127,
Visits: 348
|
|
| but that shouldnt affect from first @@rowcount value..right?.. is thr any other way to make sure a rows are inserted or updated?
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 11,647,
Visits: 27,752
|
|
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
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Wednesday, October 31, 2012 1:16 AM
Points: 127,
Visits: 348
|
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 37,740,
Visits: 30,016
|
|
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 2008, MVP 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
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 11,647,
Visits: 27,752
|
|
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
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 37,740,
Visits: 30,016
|
|
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 2008, MVP 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
|
|
|
|