Solved

how do i return the number of rows selected

Posted on 2013-05-23
11
387 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

777 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