Post reply

Passing a variable for ORDER BY/ using CASE

  • 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

  • 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..

  • 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

    🙂

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply