Solved

how do i return the number of rows selected

Posted on 2013-05-23
11
391 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

679 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