Re: Can @@ROWCOUNT return NULL?

Dimitri Furman (dfurman@cloud99.net)  writes:
> Is it possible for the @@ROWCOUNT function to return NULL after a
> statement? I am troubleshooting a relatively large stored procedure with
> multiple SELECT statements and a couple of INSERTs into table variables.
> Immediately after each statement I save the value returned by @@ROWCOUNT
> to a local variable. That information eventually is passed back to the
> client via one output parameter, for all statements in the procedure.
> Occasionally, the value returned via that parameter is NULL. This cannot
> be reproduced by re-running the SP with the same input parameters.
>
> Before doing any further troubleshooting, I would like to rule out the
> possibility that @@ROWCOUNT can actually return a NULL under some
> circumstances. From searching the archives, it appears that in SQL
> Server 7.0 this could happen in the context of a DML query on a table
> with triggers. This is not the case here - the only DML queries are
> INSERTs into table variables, all other queries in the SP are SELECTs.

I have never heard of a case where @@rowcount can return NULL. Books
Online gives one hint when @@rowcount is not good: when more than two
milliard rows can be affected. In this case, you should try
rowcount_big(). Could this apply to you?

If not, I would recommend that you start troubleshooting. If it is not
repeatable, it will certainly be difficult...


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp