When a SQL job starts, what is the value of @@ROWCOUNT?

Question

I have been looking for an answer to this question, but I believe it may be a useful piece of information for others as well.

I am working with TSQL in SQL server management studio. Due to the way our system processes information, it's desirable to do updates in smaller batches. A trick we use is to wrap updates in a while loop as such:

while (@@Rowcount <> 0)
begin
    update top (800) etc etc
end

I have created a job to do this update regularly and while this works in a query window, it does not seem to work in a job. Is the rowcount value populated when a job begins?

Answer 1

@@ROWCOUNT is 0 at the beginning of a statement, what is happening for you is that when SSMS first opens up a connection it executes a series of queries behind the scenes (you can capture the specific queries with a trace), so you get a residual value for @@ROWCOUNT of 1.

When doing batch updates like this, I tend to take a slightly different approach:

WHILE 1 = 1
BEGIN
    UPDATE  TOP (100) ....
    SET     ...


    IF @@ROWCOUNT = 0
        BREAK;
END

I don't think this has any benefit whatsoever over doing something like:

SELECT 1;
WHILE  @@ROWCOUNT > 0
BEGIN
    ...
END

And is more long winded, but doing a pointless select or assignment just feels odd to me, perhaps owing to some minor OCD.