Why does @@ROWCOUNT return 1 for a NULL statement using sp_executesql?

Question

If I run this:

DECLARE @sql NVARCHAR(10) = NULL;
EXEC sp_executesql @sql;
SELECT @@ROWCOUNT;

I would expect to get 0, maybe even NULL would make sense. But I don't get either, I get 1. Why is 1 row affected by executing a NULL query? If I pass in a "proper" (non_NULL) query then it works fine.

---

Background (for those that care): this is from a process that is supposed to generate some dynamic SQL to update one row and ONLY one row. I need to check that 1 row has been affected, not 0 or 2 or more than 2. It worked fine until somehow a NULL SQL statement managed to be generated, and this was seen as a success - oops!

The actual fix will be to check the SQL is non-NULL before running it, and treat a NULL statement the same way as a result other than 1. But I was still curious why it behaved this way.

Answer 1

It's because you're assign NULL to your variable. Statements that make a simple assignment always set the @@ROWCOUNT value to 1.

See the example below. Because management studio can run its own queries on the connection and mess with the @@ROWCOUNT value it starts off selecting an empty result set to ensure the initial @@ROWCOUNT value is zero.

When there is no assignment the SELECT @@ROWCOUNT returns 0 (the initial value has not been modified). Otherwise it returns 1

/*Ensure @@ROWCOUNT starts off at 0*/
SELECT 1 WHERE 1 = 0;

DECLARE @sql NVARCHAR(10);
EXEC sp_executesql @sql;
SELECT @@ROWCOUNT;

GO

/*Ensure @@ROWCOUNT starts off at 0*/
SELECT 1 WHERE 1 = 0;

DECLARE @sql NVARCHAR(10) = NULL;
EXEC sp_executesql @sql;
SELECT @@ROWCOUNT;

You can also try similar with a non zero initial value:

/*Ensure @@ROWCOUNT starts off at 3*/
SELECT 1 UNION SELECT 2 UNION SELECT 3

DECLARE @sql NVARCHAR(10);
EXEC sp_executesql @sql;
SELECT @@ROWCOUNT; --Returns 3

GO

/*Ensure @@ROWCOUNT starts off at 3*/
SELECT 1 UNION SELECT 2 UNION SELECT 3

DECLARE @sql NVARCHAR(10) = NULL;
EXEC sp_executesql @sql;
SELECT @@ROWCOUNT; --Returns 1

Answer 2

Please note that my answer was written purely from my experience of SQL Server Management Studio and does not accurately explain this behaviour. Martin Smith has explained why this isn't true in a comment below.

It looks like sp_executesql doesn't run at all with a null parameter, perhaps as a failsafe.

Try running "SELECT @@ROWCOUNT" alone in a batch and you'll see that it returns 1, regardless of the fact there is no current rowcount. It seems likely that 1 is the default return value and that's why you're seeing this.