I have what I think is some rather strange behavior using @@Rowcount in SQL Server 2005.
Basically, in some T-SQL I'm coding I've been trying to use @@Rowcount after an INSERT to count how many rows were affected. However, I've been finding that although the code works properly the first time I run it, on subsequent occasions it gives
me a zero count for @@Rowcount. I wrote some test code to see if this is a repeatable problem and it seems to be.
Below is my test code. Basically, it goes as follows:
- Create a table containing some lines of fake data
- Create a temp table to store the rowcount from a select statement
- Select all the rows in the fake data table and update the temp table with the value of @@Rowcount
- Show me the temp table result
- Delete the temp tables i created
Now the first time I run this code in a query window, the @@Rowcount is stored in the temp table correctly (100 rows affected). If I rerun it, the value stored is zero. All subsequent times I run it, I always get zero. If I add a blank
line, or some comment, or otherwise modify the code, sometimes it will run correctly again, but the next time gives me zero. It seems very odd.
Now I can get around it by storing @@rowcount in a variable instead of a table entr
View Complete Post