Paging with Stored Procedure
Posted by Viral Sarvaiya on September 6, 2010
Lets consider a simple table called employee( emp_id, name, salary). Now, suppose that we need to create a gridview with paging. One option is to bring all the data from database and do the paging in client-side and another one is to do selective fetch. In both cases we can use a stored procedure that takes some parameter and returns a resultset.
CREATE PROCEDURE GetEmployees @Status int, @StartIndex int, @PageSize int AS WITH FilteredList( [emp_id],[name], [salary], [RowNumber]) AS ( SELECT [emp_id], [name], [salary], ROW_NUMBER() OVER ( ORDER BY [ID] DESC) AS [RowNumber] FROM Employee ) SELECT * FROM FilteredList WHERE RowNumber BETWEEN (@StartIndex + 1) AND (@StartIndex + @PageSize)