Solved

how do i return the number of rows selected

Posted on 2013-05-23
11
389 Views
Last Modified: 2013-05-28
hi,  how do i return the number of rows selected without performing another query, lets say


select t1.name, 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.
0
Comment
Question by:rafaelrgl
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39190705
SELECT goo, foo, boo
FROM YourTable

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

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.
0
 

Expert Comment

by:lmfs
ID: 39190768
SELECT COUNT (fieldname) as Field1

from tablename

or if you want to count distinct rows:

SELECT COUNT (DISTINCT fieldname) as Field1

from tablename
0
 
LVL 1

Author Comment

by:rafaelrgl
ID: 39190893
Guys, i found this @@TOTAL_READ  so if we do it:

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


it will work for me
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:rafaelrgl
ID: 39195970
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
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39190917
@@TOTAL_READ:  The number of disk reads, not cache reads, by SQL Server since SQL Server was last started.
http://msdn.microsoft.com/en-us/library/ms187917.aspx

It is NOT the same as the number of rows returned in a SQL statement.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39190921
Not correct.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39191816
You give up too quickly -
select t1.name, t2.cnt as getnumbersofrows from
(select name from tbusers) as t1,
(select count(name) cnt
 from tbusers) as t2
;
0
 
LVL 1

Author Comment

by:rafaelrgl
ID: 39192094
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 t1.name, 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.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39192134
The only other way I can see doing that is to create a user-defined function, getnumbersofrows, to get the total count.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39195971
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.
0
 
LVL 1

Author Closing Comment

by:rafaelrgl
ID: 39196443
thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question