3

The docs for @@ROWCOUNT say:

EXECUTE statements preserve the previous @@ROWCOUNT.

I take this to mean that @@ROWCOUNT should have the same value after an EXECUTE statement that it had before.

However, considering the following:

CREATE TABLE #wat (id int);

INSERT #wat VALUES (1),(2),(3);

SELECT @@ROWCOUNT before;

EXECUTE('INSERT #wat VALUES (1),(2);');
SELECT @@ROWCOUNT after;

DROP TABLE #wat;

On SQL Server 2016 SP2 CU 8, before and after are different.

Do I misunderstand something here, or is this a documentation issue?

1
  • 1
    I think that statement is referring to Natively compiled stored procedures. Please refer following statement just before the EXAMPLE in the same @@ROWCOUNT documentation Natively compiled stored procedures preserve the previous @@ROWCOUNT. Transact-SQL statements inside natively compiled stored procedures do not set @@ROWCOUNT.
    – Squirrel
    Commented Dec 10, 2019 at 5:40

1 Answer 1

3

I propose a different test for you to understand that doc statement:

CREATE PROCEDURE TestRowCount 
AS
BEGIN
    SELECT TOP (1) name FROM sys.databases;
    SELECT TOP (6) name FROM sys.databases;
END

After you create this procedure, run this:

EXECUTE TestRowCount;
SELECT @@ROWCOUNT AS Previous;

The Previous result is 6.
With that result it's easier to understand that EXECUTE statements preserve the previous @@ROWCOUNT means the previous statement that was called by the EXECUTE.

In your example the EXECUTE called an statement that affected 2 rows, so it returned 2 instead of 3 (as you expected).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.