Row Number get Count of row returned

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

General SQL Server Forums

New to SQL Server Programming

Row Number get Count of row returned

Author

Topic

BigMeat
Yak Posting Veteran

56 Posts

Posted - 2009-02-14 : 21:28:16

Hi

I am using Sql 2008 and I am using RowNumber to page through results. But I am having abit of a problem as I need to return the total number of results found aswell. I use to use teh @@RowCount method to do this but this wont work if I am paging teh results. Do The only way I can think of doing this is to do a Count(*) and execute the statement again? Are there any other methods I can use?

Below is an example of what I am doing

WITH Providers AS
(
SELECT DISTINCT SvpName,ROW_NUMBER() over(order by SvpName) as RowNumber, etc....
FROM Complex JOIN
WHERE (SvpaAlias like @prmSvpName)
)
SELECT * FROM Providers
WHERE
RowNumber BETWEEN (@CurrentPage - 1) * @PageSize + 1 AND @CurrentPage * @PageSize
ORDER BY SvpName

However I want to get the count of total rows found and put this into a parameter. I use to do @prmCount = @@RowCount but this wont work any more.

Many thanks in advance

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-14 : 22:12:17

[code]WITH Providers AS
(
SELECT DISTINCT SvpName,ROW_NUMBER() over(order by SvpName) as RowNumber, etc....
FROM Complex JOIN
WHERE (SvpaAlias like @prmSvpName)
)
SELECT columns...,COUNT(*) OVER () AS [ROWCOUNT] FROM Providers
WHERE
RowNumber BETWEEN (@CurrentPage - 1) * @PageSize + 1 AND @CurrentPage * @PageSize
ORDER BY SvpName[/code]

BigMeat
Yak Posting Veteran

56 Posts

Posted - 2009-02-15 : 08:33:32

Hi sodeep

That is really helpful, however I wanted to put the RowCount value into a output paramter that I pass to the stored proc. I tried the following but did not work

WITH Providers AS
(
SELECT DISTINCT SvpName,ROW_NUMBER() over(order by SvpName) as RowNumber, etc....
FROM Complex JOIN
WHERE (SvpaAlias like @prmSvpName)
)
SELECT columns..., @prmResultsCount = COUNT(*) OVER () AS [ROWCOUNT] FROM Providers
WHERE
RowNumber BETWEEN (@CurrentPage - 1) * @PageSize + 1 AND @CurrentPage * @PageSize
ORDER BY SvpName

Many thanks in advance

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-15 : 17:08:38

[code]Can't you use like this?

@Rowcount int output... as parameter for store proc

then,

Select @rowcount = @@Rowcount[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-15 : 21:22:07

this will give you rowcount alonmg with resultset.


WITH Providers AS
(
SELECT DISTINCT SvpName,COUNT(SvpName) OVER () AS RowCount, ROW_NUMBER() over(order by SvpName) as RowNumber, etc....
FROM Complex JOIN
WHERE	(SvpaAlias like @prmSvpName)
)
SELECT * FROM Providers
WHERE 
RowNumber BETWEEN (@CurrentPage - 1) * @PageSize + 1 AND @CurrentPage * @PageSize
ORDER BY SvpName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources