Solved

how do i return the number of rows selected

Posted on 2013-05-23
11
395 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Question about Common Table Expressions 3 45
Report 8 27
sql server query 9 28
SQL - Using an 'array' kind of variable 5 19
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

710 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