Why Custom Paging?
Custom paging allows you to get limited number records from a large database table that saves processing time of your database server as well as your application server and makes your application scalable, efficient and fast.
In this article, I am going to explain how to create a stored procedure in SQL Server 2005 that allows you to pass startRowIndex and pageSize as a parameter and return you the number of records starting from that row index to the page size specified. It was possible in the SQL Server 2000 too but it was not as easy as in SQL Server 2005 is.
-- EXEC LoadPagedArticles 10, 5
CREATE PROCEDURE LoadPagedArticles
-- Add the parameters for the stored procedure here
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- increase the startRowIndex by 1 to avoid returning the last record again
SET @startRowIndex = @startRowIndex + 1
SELECT * FROM (
ROW_NUMBER() OVER (ORDER BY AutoID ASC) as RowNum
) as ArticleList
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1
ORDER BY AutoID ASC