CODEWITHSUNDEEP
sql-serverrowcount
Royi Namir
Royi Namir

Reputation: 148524

Scope of @@rowcount?

What is the scope of @@rowcount ?MSDN doesn't mention its scope.

Returns the number of rows affected by the last statement.

Ok.

In my SP — I'm inserting into a table , which has a insert trigger which does another insertion when a new value is inserted to the table.

Question:

To which scope the @@rowcount will refer ? (trigger or SP) ?

Upvotes: 32

Views: 29052

Answers (2)

NG.
NG.

Reputation: 6043

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

begin
declare @myrowcount int,
    @myrowcount2 int
insert stmt
SET @myrowcount=@@rowcount
if @myrowcount>0
begin
insert stmt
SET @myrowcount2 =@@rowcount
if @myrowcount2 >0
do smthg
end
end

or try this 

 SELECT * FROM master.sys.objects -- 50 rows
    IF (1=1)
        SELECT @@ROWCOUNT AS RowsAffected -- 0, because the IF did not affect any rows

even an IF statement affects it....hence its scope is the last statement read.

Upvotes: 12

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32680

Granted, the article is for SQL Server 2000, but one would hope the scope doesn't change between versions. According to the article How triggers affect ROWCOUNT and IDENTITY in SQL Server 2000, @@ROWCOUNT will not be affected by triggers.

Specifically:

It’s safe to use @@ROWCOUNT in SQL Server 2000 even when there is a trigger on the base table. The trigger will not skew your results; you’ll get what you expect. @@ROWCOUNT works correctly even when NOCOUNT is set.

So if you update three rows, and the trigger updates five rows elsewhere, you'll get a @@ROWCOUNT of 3.

Also, from GBN's answer in SQL Server - is using @@ROWCOUNT safe in multithreaded applications?:

@@ROWCOUNT is both scope and connection safe.

In fact, it reads only the last statement row count for that connection and scope.

Upvotes: 24

Related Questions