locked
Dynamic ASCDESC and dynamic orderBy field RRS feed

  • Question

  • User1130413779 posted
    SELECT ROW_NUMBER() OVER(ORDER BY
    
    
    CASE @AscDesc
    WHEN 'ASC' THEN
    CASE @OrderBy when 'O_Date' then OrderDate end ,
    case @OrderBy when 'O_Price' then GrandTotal end, 
    case @OrderBy when 'O_User' then UserName end,  
    case @OrderBy when 'O_Success' then Success end, 
    case @OrderBy when 'O_Position' then Position end
    END ASC,
    
    CASE @AscDesc
    WHEN 'DESC' THEN
    
    CASE @OrderBy when 'O_Date' then OrderDate end ,
    case @OrderBy when 'O_Price' then GrandTotal end,
    case @OrderBy when 'O_User' then UserName end,
    case @OrderBy when 'O_Success' then Success end,
    case @OrderBy when 'O_Position' then Position end
    
    END DESC
    
    ) AS RowNum


    error  

    Incorrect syntax near ','.

     

    I know the way to replace all the fildes to nvarchar than the orderby

    something like this

    CASE

    @Ordered

    WHEN 'Header' THEN c.

    header

    WHEN 'Date' THEN CONVERT(varchar,c.PublicationDate,112

    )

    ELSE c.

    header

    but i want to avoid convert the orderby data

     

    how can i filter with dynmic filed and dynmic asc/Desc

     

    thanks

    Saturday, December 18, 2010 4:02 PM

Answers

  • User1130413779 posted

    thanks

     

    the way is

              CASE WHEN @SortParam = 'Rank' AND @SortDirection = 'desc' THEN ServiceRankCode END DESC,
                CASE WHEN @SortParam = 'Rank' AND @SortDirection = 'asc' THEN ServiceRankCode END,

     

    the source:

     http://www.sitepoint.com/forums/showthread.php?t=617154 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 19, 2010 10:58 AM

All replies

  • User1130413779 posted

    and when i try convert all data to nvarchar than the order not work well because tha 800 is bigger than 5000

    because that 8 is bigger than 5

     

    any ideas?

     

    CASE @AscDesc
    WHEN 'ASC' THEN
    
    	CASE @OrderBy  
    		WHEN 'O_Date' THEN CONVERT(nvarchar,OrderDate,112 )
    		WHEN 'O_Price' THEN CONVERT(nvarchar,GrandTotal)
    		WHEN 'O_User' THEN  UserName
    		WHEN 'O_Success' THEN CONVERT(nvarchar,Success)
    		WHEN 'O_Position' THEN CONVERT(nvarchar,Position)
    		ELSE  CONVERT(nvarchar,EntityId) 
    	end	
    END ASC,


     

    Sunday, December 19, 2010 2:54 AM
  • User1224194097 posted

    how can i filter with dynmic filed and dynmic asc/Desc

    Build a dynamic SQL, Check this example

    DECLARE @SortExpression VarChar(100);
    SET @SortExpression='[Name]';
    DECLARE @SortOrder VarChar(100);
    SET @SortOrder='DESC';

    EXEC('SELECT *, 
                 ROW_NUMBER() OVER(ORDER BY '+ @SortExpression +' '+ @SortOrder +') AS RowNum
    FROM
    (
    SELECT 100 ID, ''Test 1'' [Name]
    UNION
    SELECT 200 ID, ''Test 2'' [Name]
    UNION
    SELECT 300 ID, ''Test 3'' [Name]
    UNION
    SELECT 400 ID, ''Test 4'' [Name]
    UNION
    SELECT 500 ID, ''Test 5'' [Name]  
    )A');


    Sunday, December 19, 2010 2:57 AM
  • User1130413779 posted

    no its a complicate query with many filters fields and paging

    and all the query work well just the order and i try to resolve this problem with no dynamic SQL

     

     

    Thanks!

    Sunday, December 19, 2010 5:26 AM
  • User1130413779 posted

    thanks

     

    the way is

              CASE WHEN @SortParam = 'Rank' AND @SortDirection = 'desc' THEN ServiceRankCode END DESC,
                CASE WHEN @SortParam = 'Rank' AND @SortDirection = 'asc' THEN ServiceRankCode END,

     

    the source:

     http://www.sitepoint.com/forums/showthread.php?t=617154 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 19, 2010 10:58 AM