May 20, 2011 at 7:47 am
I need to retrieve the @@RowCount value from within a proc like so:
select @RowCount = @@RowCount From tTable Where....
This works if it's not called from within a proc however as soon as I attempt to do this inside a proc it returns 0 as the Rowcount.
I know I can set the rowcount after the select statment but I just thought it was interesting that it doesn't work like this inside a proc.
Any ideas why or if it can work inside a proc the way I'm attempting to do it?
May 20, 2011 at 9:03 pm
It's totally wrong use of the function.
Read BOL about correct ways to use it.
_____________
Code for TallyGenerator
May 21, 2011 at 2:52 am
May 23, 2011 at 6:08 am
I was just trying to capture a result if there was data present. If I use @@Rowcount I usually do it like this:
select ID from tTable Where....
SET @RowCount = @@RowCount....
What lead me to using it in the actual select statement was I didn't want to return a record set so I thought I'd try it like this:
select @Rowcount = @@Rowcount from tTable Where....
Which to my surprise did work as long as it wasn't in a proc.
Right now I'm just capturing the ID like so:
select @ID = ID from tTable Where ....
May 23, 2011 at 6:13 am
It's not supposed to work like that. It's supposed to be the very next statement after the select >> set @rc = @@ROWCOUNT.
My guess is that in your query window you already have run a select statement and the variable is set instead of being null (or maybe 0 as default... not sure there).
May 23, 2011 at 7:47 am
Ninja,
Yeah I normally do it like that but I just stumbled across this and decided to waste everyone's time posting I guess.
But decided to run a quick test after you mentioned me using a prior select statement.
Open up a new query window with no other select statements aside from the @@RowCount one:
Declare @rc int
select @rc = @@RowCount from tTablel Where ID = ????
PRINT isnull(@RC,0)
If your query does produce at least one row the @@RowCount will show 1.
I ran a query which would return multiple rows and the row count was still only one. All other subsequent executions result in 0.
Another completely useless discovery! Yaaaaa! 😀
Okay, back to something important.
May 23, 2011 at 8:24 am
Maybe you should post as QOTD... nice piece of trivia!
May 23, 2011 at 10:36 am
Straight from BOL:
Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT 'Generic Text'.
Basically, this would be the case for "select @Rowcount = @@Rowcount from tTable". It turns a query into a variable assignment leading to the return value 1. The original value of
@@Rowcount is replaced by the pre-defined value of 1 for a variable assignment.
Regarding the subsequent return value Zero: it's most probably caused by the missign initialization. As soon as the code is changed to
Declare @rc int
select @rc = NULL
select @rc = @@RowCount from tTablel Where ID = ????
PRINT isnull(@RC,0)
The return value is consistent.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply