problem on @@ROWCOUNT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

problem on @@ROWCOUNT

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2011-08-15 : 08:12:09

i got this query


UPDATE tts
	set tts.Company=t3.IndexId
	from TargetTable_Staging tts
	inner join ( 
	select t1.id as PKid,t2.id as IndexId
	FROM TargetTable t1 inner join Dyn_Company t2 (NOLOCK)
	ON t1.Company=t2.Name  COLLATE SQL_Latin1_General_CP1_CI_AS )t3 on tts.id=t3.PKid

after the query i try to get the row count :
Select @intErrorCode=@@ERROR,@intRowsCount=@@ROWCOUNT
i get wrong number on @intRowsCount
but when i query it i get the correct result (it takes few seconds between the query executed inside a Stored procedure, and when i manually query the table
any idea?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-15 : 11:27:52

You've got a piece of code in between the UPDATE statement and the @@ROWCOUNT. That's the only way to get the wrong count.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-16 : 05:48:12

Do triggers also screw up @@ROWCOUNT?

If so is there a trigger on the table?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-16 : 05:51:43

Scratch that. Douesn't look like UPDATE triggers do modify @@ROWCOUNT. Stupid example:


CREATE TABLE foo (
	[val] INT
	)
GO

CREATE TRIGGER fooTrigger ON foo
FOR UPDATE AS
	DELETE FROM foo

GO
INSERT foo VALUES (1),(2),(3)
SELECT @@ROWCOUNT

UPDATE foo SET [val] = 0 WHERE [val] = 1
SELECT @@ROWCOUNT

SELECT * FROM foo

Second @@ROWCOUNT still only returns a value of 1 not 3

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources