Custom paging,sorting and filtering data with Dynamic query

Sometime it becomes very essential for us to write dynamic query for custom paging,sorting and filtering data. Today I want to share simple one which may help others to fulfill their requirements.


Sample stored procedure:


CREATE PROCEDURE [dbo].[spGetAllTag]
@SearchText varchar(50),
@PageIndex INT = 1,//page number like 1,2,3
@PageSize INT =10,
@SortCol varchar(100),
@TotalCount int output
As
Begin

;WITH PagingCTE (Row_ID,ID,Tags,Weight,CreatedBy)
     AS
      (
      SELECT
            ROW_NUMBER()
                  OVER(ORDER BY
                         CASE WHEN @SortCol='Name DESC' THEN [NAME] END DESC,
                         CASE WHEN @SortCol='Name ASC'  THEN [NAME] END ASC,
                         CASE WHEN @SortCol='CreatedBy DESC' THEN CreatedBy END DESC,
                         CASE WHEN @SortCol='CreatedBy ASC'  THEN CreatedBy END ASC,
                         CASE WHEN @SortCol='Weight ASC'   THEN Weight  END ASC,
                         CASE WHEN @SortCol='Weight DESC'  THEN Weight  END DESC

                        ) AS [Row_ID],
ID,
[NAME],
Weight,
CreatedBy

FROM TabularWeight
WHERE [Name] LIKE @SearchText + '%'
         )

       SELECT
            Row_ID,
            ID,
            [NAME],
            Weight,
            CreatedBy
         
      FROM PagingCTE
      WHERE Row_ID >= (@PageSize * @PageIndex) - (@PageSize -1) AND
            Row_ID <= @PageSize * @PageIndex

select @TotalCount = count(1) from dbo.TabularWeight where [Name] LIKE @SearchText + '%'  
   
End
GO