Help ! How do I get the total number rows from my mssql paging procedure ?
- by The_AlienCoder
Ok I have a table in my MSSQL database that stores comments. My desire is to be able to page though the records using [Back],[Next], page numbers & [Last] buttons in my datalist. I figured the most efficient way was to use a stored procedure that only returns a certain number of rows within a partcular 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 datalist pager.Now I need the total number of all comments(not in the cuurent 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 doesnt 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.
