Passing a variable for ORDER BY/ using CASE

• Arun Kumr Singh

  Grasshopper

  Points: 10

  More actions

  March 1, 2012 at 7:09 am

  #1453602

  Resolution is specific to SQL SERVER

  Explanation:

  1. Ordering the row number on the basis of param

  2. Againt ordering the row number in outer query

  Try this code

  DECLARE @PageNum int

  DECLARE @PageSize int

  DECLARE @TotalRowsNum int

  DECLARE @SortColumn varchar(200)

  DECLARE @SortOrder varchar(5)

  SET @PageNum = 4;

  SET @PageSize = 10;

  SET @SortColumn = 'CODE_ID';

  SET @SortOrder = 'DESC';

  WITH QueryResult AS

  (

  SELECT *,

  CASE @SortOrder WHEN 'ASC' THEN

  ROW_NUMBER() OVER(ORDER BY @SortColumn ASC)

  ELSE

  ROW_NUMBER() OVER(ORDER BY @SortColumn DESC)

  END AS 'RowNumber'

  FROM TABLE_NAME

  )

  SELECT * FROM QueryResult

  WHERE RowNumber BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize

  ORDER BY RowNumber ASC

• roberto.rabino

  SSC Enthusiast

  Points: 160

  More actions

  March 2, 2012 at 4:07 am

  #1454086

  add to your query:

  order by case when @sortby = 'ID' then id else case when @sortby = 'Size' then size ...end end

  * case when @sort = 'asc' then 1 else -1 end

  I suppose it's right..

• Arun Kumr Singh

  Grasshopper

  Points: 10

  More actions

  March 2, 2012 at 5:36 am

  #1454104

  I haven't check your code and not saying that is not right.

  I am not comparing the Sort Column,

  It is generic code for paging and sorting by any Column

  comparing only the sort order

  🙂