Anyway to get a value similar to @@ROWCOUNT when TOP is used?

Posted by cfeduke on Stack Overflow See other posts from Stack Overflow or by cfeduke
Published on 2010-04-22T15:19:56Z Indexed on 2010/04/22 16:33 UTC
Read the original article Hit count: 195

Filed under:
|

If I have a SQL statement such as:

SELECT TOP 5 
* 
FROM Person 
WHERE Name LIKE 'Sm%'
ORDER BY ID DESC

PRINT @@ROWCOUNT
-- shows '5'

Is there anyway to get a value like @@ROWCOUNT that is the actual count of all of the rows that match the query without re-issuing the query again sans the TOP 5?

The actual problem is a much more complex and intensive query that performs beautifully since we can use TOP n or SET ROWCOUNT n but then we cannot get a total count which is required to display paging information in the UI correctly. Presently we have to re-issue the query with a @Count = COUNT(ID) instead of *.

© Stack Overflow or respective owner

Related posts about t-sql

Related posts about sql-server