By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,602 Members | 1,533 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,602 IT Pros & Developers. It's quick & easy.

Can @@ROWCOUNT return NULL?

P: n/a
SQL Server 2000 SP3.

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.

Any related information would be appreciated.

--
remove a 9 to reply by email
Aug 29 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Dimitri Furman (df*****@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, es****@sommarskog.se

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

Aug 29 '05 #2

P: n/a
Try removing
SET NOCOUNT ON
from the stored procedure.
GeoSynch
"Dimitri Furman" <df*****@cloud99.net> wrote in message
news:Xn****************************@127.0.0.1...
SQL Server 2000 SP3.

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.

Any related information would be appreciated.

--
remove a 9 to reply by email

Aug 30 '05 #3

P: n/a
GeoSynch (Sp********@Casablanca.com) writes:
Try removing
SET NOCOUNT ON
from the stored procedure.


@@rowcount should always return a value even if NOCOUNT is on. This
option controls whether rowcount information is passed to the client.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

Aug 30 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.