Pagination By name + SQL Server 2008

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 343
CREATE PROC USP_PaginationByName
@PageSize int, @PageNumber int
AS
Declare @RowStart int
Declare @RowEnd int
if @PageNumber > 0
Begin
SET @PageNumber = @PageNumber -1
SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1 ;

With CityCte AS
( SELECT CityID, CityName,
ROW_NUMBER() OVER (order by CityName) as RowNumber
FROM city )
select *
from CityCte
Where RowNumber >= @RowStart and RowNumber <= @RowEnd
end
GO

--Testing SP
exec USP_PaginationByName 10, 1 -- 1st page with 10 records
exec USP_PaginationByName 10, 2 -- 2nd page with 10 records


NOTE: SP is using a table called 'City'. Any one can tweet the above SP by replacing table with yours

Comments or Responses

Login to post response