Pagining thru stored procedure

cmanva

By default if you have a table with 100 rows (with paging enabled). It will display 10 rows at a time, by slicing up the 100 rows by 10.

I have large results that causes long load time. So, I wanted a stored procedure to handle the paging.
Not sure how to work it with the control. For example, my stored procedure is the following:

[code]
CREATE PROCEDURE GetTowns ( @OutTotalRecCount INT OUTPUT, @CurrentPage INT, @PageSize INT )
AS
SELECT * FROM
( SELECT ROW_NUMBER() OVER (ORDER BY TownName) AS Row,
TownId,
TownName
FROM Towns
) AS TownsWithRowNumbers
WHERE Row >= (@CurrentPage - 1) * @PageSize + 1 AND Row <= @CurrentPage*@PageSize

SELECT @OutTotalRecCount = COUNT(*) FROM Towns
[/code]


I pass what page I am viewing currently (@CurrentPage) and how many to display at a time (@PageSize).
My problem if I pass @CurrentPage=1 and @PageSize=20. The DataTable control will displays "Showing 1 to 20 of 20 entries" and 1 page. There are 1000 results if not limited to 20 per page. How do I tell the control there 1000 results which will result in:

Showing 1 to 20 of 1000 entries
1 2 3 4 5 6 7... Next Last

Also, when I click to Page 2 or Page 3..etc.. How do I grab the value that tells me I am at page 2,3,4 5, etc... so I can pass it to my stored procedure? Also how do it rebind it to display Page 2 when I get the results from stored procedure?

Note: Classic ASP, SQL Server 2005

Thanks

mg

Just in case, have you already viewed the documentation here?

http://datatables.net/usage/server-side

and the classic asp example here?

http://datatables.net/development/server-side/asp_classic