SQL 2005 Paging

You can find many ways to implement the paging in the stored procedures. Here is the one that I learned from my colleague.

Create PROCEDURE [dbo].[getMemberInfo] (
@pageNumber int = 1, --which page you want to fetch
@rowsPerPage int = 50 --how many rows per page
)
AS
BEGIN
SET NOCOUNT ON;

Declare @startRowIndex int,
@endRowIndex int

Set @startRowIndex = ((@pageNumber - 1) * @rowsPerPage) + 1
Set @endRowIndex = @startRowIndex + @rowsPerPage - 1

Select * 
From (
Select *,
row_number() Over (Order By accountName) As rownum
From dbo.Member
)
Where rownum Between @startRowIndex And @endRowIndex
End
GO