How to create custom search & optimize paging stored procedure (SP)

Here I am going to implement SP which provides Optimize Paging for tabular data representation and also custom search.

First step to create one sql table Users by below sql script

 CREATE TABLE [dbo].[Users](  
      [USR_User_ID] [bigint] IDENTITY(1,1) NOT NULL,  
      [USR_Department_ID] [bigint] NOT NULL,  
      [USR_FirstName] [nvarchar](100) NOT NULL,  
      [USR_MiddleName] [nvarchar](100) NULL,  
      [USR_LastName] [nvarchar](100) NOT NULL,  
      [USR_Email] [nvarchar](100) NULL,  
      [USR_Username] [nvarchar](100) NOT NULL,  
      [USR_Password] [nvarchar](200) NOT NULL,  
      [USR_Contact_No] [nvarchar](20) NULL,  
      [USR_Last_Login] [datetime] NOT NULL,  
      [USR_Status] [bit] NOT NULL,  
      [USR_Created_Date] [datetime] NOT NULL,  
      [USR_Created_By] [bigint] NOT NULL,  
      [USR_Updated_Date] [datetime] NULL,  
      [USR_Updated_By] [bigint] NULL,  
      [USR_IsDeleted] [bit] NOT NULL,  
      [USR_IsAdmin] [bit] NOT NULL,  
  CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED   
 (  
      [USR_User_ID] ASC  
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
 ) ON [PRIMARY]  

I am going to add search parameters for
•    Username
•    Name
•    Status
•    DepartmentId

 /* Optional Filters for Dynamic Search*/  
   @Username NVARCHAR(50) = NULL,  
   @Name NVARCHAR(50) = NULL,  
   @Status tinyint=2,  
   @DepartmentId bigint,  

I am going to add pagination parameters
•    PageNo
•    PageSize

 /*– Pagination Parameters */  
   @PageNo INT = 0,  
   @PageSize INT = 10,  

Now adding dynamic filter expression

   @filterExpression NVARCHAR(max) = NULL,  

Now adding column sort parameters
•    SortColumn
•    SortOrder

 /*– Sorting Parameters */  
   @SortColumn NVARCHAR(50) = 'USR_User_ID',  
   @SortOrder NVARCHAR(4)='DESC',  

Now adding export flag parameter for return all the data

 /*– Export to excel flag */  
   @Export BIT = 0,  

Now adding total count output parameters for total no of records after applying filter parameters

 /*– Output Parameters */  
   @TotalCount INT OUTPUT  

Declare variables

 DECLARE @SQLQuery NVARCHAR(MAX)='';   
 DECLARE @SQLCountQuery NVARCHAR(MAX)='';  
 DECLARE @WhereClause NVARCHAR(MAX) = '';  
 DECLARE @OffsetRows INT;  

Set values to variables

 SET @OffsetRows = @PageNo * @PageSize;  
 SET @SQLQuery = 'SELECT * FROM Users U WHERE USR_IsDeleted=0 ';  
 SET @SQLCountQuery = 'SELECT COUNT(*) FROM Users U WHERE USR_IsDeleted=0 ';  

Set filter expression to where clause

 IF(@filterExpression IS NOT NULL AND @filterExpression !='')  
           SET @WhereClause = @WhereClause + @filterExpression;  

Set search parameters to where clause

 IF(@Username IS NOT NULL AND @Username !='')  
           SET @WhereClause = @WhereClause + ' AND USR_Username LIKE ''%' + @Username +'%''';                 
      IF(@Name IS NOT NULL AND @Name !='')  
           SET @WhereClause = @WhereClause + ' AND (U.USR_FirstName LIKE ''%' + @Name + '%'' OR U.USR_LastName LIKE ''%' + @Name + '%'' OR U.USR_FirstName + '' '' + U.USR_LastName LIKE ''%' + @Name + '%'')';  
      IF(@Status != 2)  
           SET @WhereClause = @WhereClause + ' AND U.USR_Status = ' + CAST(@Status AS NVARCHAR(10)) + ''  
      IF(@DepartmentId != 0)  
           SET @WhereClause = @WhereClause + ' AND U.USR_Department_ID  
  = ' + CAST(@DepartmentId AS NVARCHAR(50));  

Build both sql queries

 SET @SQLQuery = @SQLQuery + @WhereClause  
 SET @SQLCountQuery = @SQLCountQuery + @WhereClause  

Execute build sql query base on column sort parameters, export parameter & paging parameters
 

 IF(@Export = 1)  
           BEGIN  
                SET @SQLQuery = @SQLQuery + ' ORDER BY '+ @SortColumn + ' ' + @SortOrder ;  
           END  
      ELSE  
           BEGIN  
                SET @SQLQuery = @SQLQuery + ' ORDER BY '+ @SortColumn + ' ' + @SortOrder +' OFFSET '+ CAST(@OffsetRows AS NVARCHAR(10)) +' ROWS FETCH NEXT '+ CAST(@PageSize AS NVARCHAR(10)) +' ROWS ONLY';  
           END  

 DECLARE @RowCount TABLE (Value int);  
      INSERT INTO @RowCount  
      EXECUTE(@SQLCountQuery)  
      SELECT @TotalCount = Value FROM @RowCount;  
 EXECUTE(@SQLQuery)