COUNT ROWS RETURNED BY A QUERY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

COUNT ROWS RETURNED BY A QUERY

Author

Topic

rajivlodha
Starting Member

1 Post

Posted - 2004-08-22 : 04:03:28

Hello All,

My Query is -

SELECT PRODUCT , COUNT (*) AS COUNT_ITEMS FROM PRODS GROUP BY PRODUCT

it returns -

PRODUCT COUNT_ITEMS
-------------------- -----------
SEAGATE_HDD_120 11
SAMSUNG_HDD_120 20

1 Row(s) affected

However, I want to count the number of rows returned by this query (2 in this case but there can be 1000 such records ).

currently, i perform a loop and count the items .. which is bad .. i want to know if this can be done thru sql ?

-

nCtr = 0
While Not oRSet.EOF
nCtr = nCtr + 1
oRSet.Movenext
Wend

Thank You for your help ..

Rajiv

maydo
Starting Member

20 Posts

Posted - 2004-08-22 : 04:23:57

SELECT @@ROWCOUNT
Returns the number of rows affected by the last statement.

You could also use the RecordCount properti of the ADO.RecordSet Object

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-22 : 07:18:26

But the ADO recordcount only works for client-side recordsets.

maydo
Starting Member

20 Posts

Posted - 2004-08-22 : 12:28:07

Well this is a client-side script and instead of looping through the recordset it is better to use the RecordCount property of ADO.Recordset.

quote:
Originally posted by rajivlodha

-

nCtr = 0
While Not oRSet.EOF
nCtr = nCtr + 1
oRSet.Movenext
Wend

Rajiv

@@ROWCOUNT is the Server-side solution .
Either as output parameter in a stored procedure or as second statement (separeted with ;) in a ad-hoc query.
ADO supports retriving results from multi-statement queries

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources