Dynamic ASCDESC and dynamic orderBy field

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

Answer 1

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,

 

Answer 2

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');

Answer 3

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!

Answer 4

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