Custom paging by stored procedure

When we need to display thousands of rows. It is better to handle paging by the stored procedure rather than load all records. I will pass parameters like @OrderBy, @OrderDir, @PageNumber, @PageSize to filter data.

Paging Example


Data base table Schema :

CREATE TABLE [dbo].[Users](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar(max)] NOT NULL,
    [DateInserted] [datetime] NOT NULL,
    [DateUpdated] [datetime] NULL,
    [LoginID] [int] NOT NULL,
    [EmailAddress] [nvarchar](max) NULL,
    [AddressID] [int] NOT NULL,
    [DateOfBirth] [datetime] NULL,
    [MobilePhone] [varchar](20) NULL,
    [Telephone] [varchar](20) NULL,
    [FirstName] [nvarchar](max) NOT NULL,
    [LastName] [nvarchar](max) NOT NULL,
    [Status] [varchar](max) NOT NULL,
    [OwnerUserID] [int] NOT NULL,
    [ProfilePic] [varchar](500) NULL,
    [GooglePlusProfileURL] [varchar](2000) NULL,
    [AboutUser] [varchar](max) NULL,
 CONSTRAINT [PK_Users_1] PRIMARY KEY CLUSTERED 
(
    [UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

User table has around 50,000 records. Apply filter & paging of records by the stored procedure. 

Stored Procedure

Create PROCEDURE [sp_GetUsers]
    @UserID INT = 0 ,
    @UserName NVARCHAR(MAX) = '' ,
    @FirstName NVARCHAR(MAX) = '' ,
    @LastName NVARCHAR(MAX) = '' , 
    @OrderBy NVARCHAR(500) = 'UserName' ,
    @OrderDir NVARCHAR(500) = 'ASC' ,
    @PageNumber INT = 0 ,
    @PageSize INT = 0
AS 
    BEGIN
        SET NOCOUNT ON ;
        WITH    UserData
                  AS ( SELECT   U.UserID ,
                                U.DateInserted ,
                                U.DateUpdated ,
                                U.LoginID ,
                                U.EmailAddress ,
                                U.AddressID ,
                                U.DateOfBirth ,
                                U.MobilePhone ,
                                U.Telephone ,
                                U.FirstName ,
                                U.LastName ,
                                U.Status ,
                                U.UserName ,
                                U.ProfilePic ,
                                U.FirstName + ' ' + U.LastName UserFullName ,
                                U.GooglePlusProfileURL ,
                                ISNULL(U.AboutUser, '') AboutUser ,
                                U.OwnerUserID ,
                                ROW_NUMBER() OVER ( ORDER BY CASE
                                                              WHEN @OrderDir = 'DESC'
                                                              THEN CASE
                                                              WHEN @OrderBy = 'UserName'
                                                              THEN u.UserName
                                                              WHEN @OrderBy = 'LastName'
                                                              THEN u.LastName
                                                              WHEN @OrderBy = 'FirstName'
                                                              THEN u.FirstName
                                                              WHEN @OrderBy = 'EmailAddress'
                                                              THEN u.EmailAddress
                                                              END
                                                             END DESC, CASE
                                                              WHEN @OrderDir = 'ASC'
                                                              THEN CASE
                                                              WHEN @OrderBy = 'UserName'
                                                              THEN u.UserName
                                                              WHEN @OrderBy = 'LastName'
                                                              THEN u.LastName
                                                              WHEN @OrderBy = 'FirstName'
                                                              THEN u.FirstName
                                                              WHEN @OrderBy = 'EmailAddress'
                                                              THEN u.EmailAddress
                                                              END
                                                              END ASC, CASE
                                                              WHEN @OrderDir = 'ASC'
                                                              THEN CASE
                                                              WHEN @OrderBy = 'DateInserted'
                                                              THEN u.DateInserted
                                                              END
                                                              END ASC, CASE
                                                              WHEN @OrderDir = 'DESC'
                                                              THEN CASE
                                                              WHEN @OrderBy = 'DateInserted'
                                                              THEN u.DateInserted
                                                              END
                                                              END DESC ) RowNumber
                       FROM    Users AS U                                
                       WHERE    @UserID IN ( U.UserID, 0 )
                                AND @UserName IN ( U.UserName, '' )
                                AND @FirstName IN ( U.FirstName, '' )
                                AND @LastName IN ( U.LastName, '' )
                     )
            SELECT  UserID ,
                    DateInserted ,
                    DateUpdated ,
                    LoginID ,
                    EmailAddress ,
                    AddressID ,
                    DateOfBirth ,
                    MobilePhone ,
                    Telephone ,
                    FirstName ,
                    LastName ,
                    Status ,
                    UserName ,
                    ProfilePic ,
                    UserFullName ,
                    GooglePlusProfileURL ,
                    AboutUser ,
                    OwnerUserID ,
                    ( SELECT    COUNT(UserID)
                      FROM      UserData
                    ) TotalRecords
            FROM    UserData
            WHERE   ( RowNumber BETWEEN ( ( @PageNumber - 1 ) * @PageSize + 1 )
                                AND     ( ( @PageNumber - 1 ) * @PageSize
                                          + @PageSize ) )
                    OR ( @PageSize = 0
                         OR @PageNumber = 0
                       )
            ORDER BY RowNumber ASC ;       
     
    END

Parameters Description

@UserID	UserID is 0 then it returns all User else Passed userid will return.
@UserName	UserName is blank then it returns all User else Passed UserName will return.
@FirstName	FirstName is blank then it returns all User else Passed FirstName will return.
@LastName	LastName is blank then it returns all User else Passed LastName will return.
@OrderBy	Filter by given OrderBy parameter
@OrderDir	Direction is either ASC or DESC
@PageNumber	PageNumber is 0 then returns all row
@PageSize	PageSize is 0 then returns all row