Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Scope of @@rowcount Expand / Collapse
Author
Message
Posted Tuesday, October 12, 2010 2:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
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?
Post #1002593
Posted Tuesday, October 12, 2010 2:12 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
Yes. As in Yes it will get effected.

/T
Post #1002594
Posted Tuesday, October 12, 2010 5:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
but that shouldnt affect from first @@rowcount value..right?.. is thr any other way to make sure a rows are inserted or updated?
Post #1002679
Posted Tuesday, October 12, 2010 5:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 12,923, Visits: 32,313
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
Post #1002682
Posted Tuesday, October 12, 2010 5:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
Thanks Lowell!!!!
Post #1002706
Posted Tuesday, October 12, 2010 6:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 40,411, Visits: 36,861
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

Post #1002729
Posted Tuesday, October 12, 2010 6:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 12,923, Visits: 32,313
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
Post #1002735
Posted Tuesday, October 12, 2010 6:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 40,411, Visits: 36,861
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

Post #1002765
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse