can @@rowcount be overwritten?

Question

can @@rowcount be overwritten?

Tobias Kiemes

SSCommitted

Points: 1604
More actions
September 9, 2015 at 7:26 am

#304232

Hello,
I have a framework where dynamic generated sql is executed like the following:
exec sp_executesql @p_command
set @p_affected_rows = @@rowcount
The query in @p_command was usually one statement. With the aim to make the transaction less big, I changed the sql to the following form:
while
process a chunk
end
This way, the method of retrieving @@rowcount will not work anymore. If I count the processed rows within my while-loop, can I somehow pass the sum to @@rowcount?
Thanks,
Tobias

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No. @@rowcount returns the number of rows affected by the previous statement, that's it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You just accumulate the row counts. Be sure to initialize your variable to zero to avoid getting a null value.

while

process a chunk

set @p_affected_rows = @p_affected_rows + @@rowcount

end

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Tobias Kiemes SSCommitted Points: 1604 More actions · Answer 3

Luis Cazares (9/9/2015)
You just accumulate the row counts. Be sure to initialize your variable to zero to avoid getting a null value.
while
process a chunk
set @p_affected_rows = @p_affected_rows + @@rowcount
end

Yes, the problem is, that this code will be contained parameter @p_command. I am looking for a way to get the rowcount after sp_executesql was executed. I was looking for an easy way without having to rewrite the framework, but this seems to be impossible.

drew.allen SSC Guru Points: 76998 More actions · Answer 4

sp_executesql allows for an OUTPUT parameter. You can see an example at sp_executesql (Transact-SQL)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA