Powerfull Paging with Stored Procedure

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.

Let’s see the following…

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)

Leave a Reply

Your email address will not be published.