Is @@ROWCOUNT preserved by EXECUTE or not?

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?

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).

StackExchange Database Administrators Q#255243, answer score: 3