how do i return the number of rows selected

hi,  how do i return the number of rows selected without performing another query, lets say

select, getnumbersofrows from tb_users where ......

how do i return on this column the numbers of rows that were selected without making another select on the table.
Who is Participating?
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
SELECT goo, foo, boo
FROM YourTable

-- This will give you the number of rows returned in the previous DML query.

So if you want to return that value, you can always stick it into a variable, and at the end of your SP do a SELECT @your_variable, or add it as a column, or whatever.
SELECT COUNT (fieldname) as Field1

from tablename

or if you want to count distinct rows:

SELECT COUNT (DISTINCT fieldname) as Field1

from tablename
rafaelrglAuthor Commented:
Guys, i found this @@TOTAL_READ  so if we do it:

select, @@TOTAL_READ as getnumbersofrows from tb_users where ......

it will work for me
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

rafaelrglAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for rafaelrgl's comment #a39190893

for the following reason:

the answer for the question was not provide
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
@@TOTAL_READ:  The number of disk reads, not cache reads, by SQL Server since SQL Server was last started.

It is NOT the same as the number of rows returned in a SQL statement.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Not correct.
You give up too quickly -
select, t2.cnt as getnumbersofrows from
(select name from tbusers) as t1,
(select count(name) cnt
 from tbusers) as t2
rafaelrglAuthor Commented:
yeah, what a rush. but, lets continue, i don't want to perform another select to get the count, is there anyway to do while selecting. i have something like this:

select top 100 t1.* from (select, getnumbersofrows from tb_users where ......) as t1 where .......

i have one query inside another, so i can't use the @@ROWCOUNT since it will give me 100 rows after i perform this query above, and i want to get the query inside the query, i don't know if i was clear with my writing.
The only other way I can see doing that is to create a user-defined function, getnumbersofrows, to get the total count.
Anthony PerkinsCommented:
The author has requested the question be closed by accepting a non-solution (their own).

Re-opening to allow the author to accept a valid solution or request the question be deleted.
rafaelrglAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.