Paging in Stored procedure

                                    Paging in Stored procedure 


ALTER PROCEDURE [dbo].[GetStoreList]
    @PageSize int ,
    @PageNumber int,
@StoreName varchar(200)=null,
@PartType int=null,
@Status int=null

AS
BEGIN
IF(@PartType =4)
BEGIN
SET @PartType = Null
END

DECLARE @upperBound INT

IF @PageNumber  < 1 SET @PageNumber = 0                                
 SET @PageNumber  = @PageNumber  + 1                                
 IF @pageSize < 1 SET @pageSize = 1                                  
 SET @upperBound = @PageNumber + @pageSize

IF(@StoreName is not null)
BEGIN
SET @StoreName = '%' +  @StoreName + '%';
END

IF @PageNumber > 0
BEGIN
SET NOCOUNT ON;

    SELECT * FROM
(  
  SELECT
  [StoreId]
      ,[LoginId]
      ,[EmailId]
      ,[StoreName]
      ,[Address]
      ,[City]
      ,[State]
      ,[Country]
      ,[ZipCode]
      ,[ContactName]
      ,[ContactNo]
      ,[PartType]
      ,[Status]
      ,[PayType]
      ,[EnquiryCount]
 ,AP.Text AS PartTypeTxt
 ,ROW_NUMBER() OVER (order by CreatedDate Desc) as RowNumber
 , COUNT([StoreId]) OVER() AS TotalRecords
FROM Store S INNER JOIN ApplicationMaster AP ON S.PartType = AP.Value
WHERE StoreName LIKE ISNULL(@StoreName,StoreName)
AND PartType = ISNULL(@PartType, PartType)
AND [Status] = ISNULL(@Status, [Status])
) AS DT
Where DT.RowNumber >= @PageNumber and DT.RowNumber <= @upperBound

END
END