AspBucket offers ASP.NET, C#, VB, Jquery, CSS, Ajax, SQL tutorials. It is the best place for programmers to learn

Saturday 12 December 2015

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

   1 comment :

  1. Thanks I can use it with custom Grid view paging.

    ReplyDelete

  • Popular Posts
  • Comments