Stored procedure for pagination in SQL Server 2005

SheoNarayan
Posted by SheoNarayan under Sql Server category on | Views : 6354
Pagination can be achieved through GridView and DataGrid controls too in asp.net but whey you have large number of data to retrieve from database they are not recommended because of performance issue. Here is the stored procedure in SQL Server 2005+ that can be used to paginate the results through database.


CREATE PROCEDURE LoadPagedArticles
@startRowIndex int,
@pageSize int
AS
BEGIN
SET @startRowIndex = @startRowIndex + 1
BEGIN
SELECT * FROM (
Select *, ROW_NUMBER() OVER (ORDER BY AutoID ASC) as RowNum
FROM Articles ) as ArticleList
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1
ORDER BY AutoID ASC
END
END
GO


To know how to implement this, please go through following article http://www.dotnetfunda.com/articles/article95.aspx

Thanks

Comments or Responses

Login to post response