Stored procedure for pagination in SQL Server 2005

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