Help ! How do I get the total number rows from my SQL Server paging procedure ?
- by The_AlienCoder
Ok I have a table in my SQL Server database that stores comments. My desire is to be able to page though the records using [Back],[Next], page numbers & [Last] buttons in my data list. I figured the most efficient way was to use a stored procedure that only returns a certain number of rows within a particular range. Here is what I came up with
@PageIndex INT,
@PageSize INT,
@postid int
AS
SET NOCOUNT ON
begin
WITH tmp AS (
SELECT comments.*, ROW_NUMBER() OVER (ORDER BY dateposted ASC) AS Row
FROM comments
WHERE (comments.postid = @postid))
SELECT tmp.*
FROM tmp
WHERE Row between
(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
end
RETURN
Now everything works fine and I have been able implement [Next] and [Back] buttons in my data list pager. Now I need the total number of all comments (not in the current page) so that I can implement my page numbers and the[Last] button on my pager. In other words I want to return the total number of rows in my first select statement i.e
WITH tmp AS (
SELECT comments.*, ROW_NUMBER() OVER (ORDER BY dateposted ASC) AS Row
FROM comments
WHERE (comments.postid = @postid))
set @TotalRows = @@rowcount
@@rowcount doesn't work and raises an error. I also cant get count.* to work either.
Is there another way to get the total amount of rows or is my approach doomed.
