Posts

Showing posts from May, 2012

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,
               …