Answered by:
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
errorIncorrect 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/DescBuild 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