February 12, 2010 at 1:23 pm
I have a block of code in my stored procedure that allows for dynamic ORDER BY. However, I cannot use ASC or DESC as it causes an error. Is there a way to include/use ASC and DESC in code like this?
Thanks!
ORDER BY CASE WHEN @SortOrder = 1 THEN eName
WHEN @SortOrder = 2 THEN eDisplay
WHEN @SortOrder = 3 THEN pName
WHEN @SortOrder = 4 THEN sLastName
WHEN @SortOrder = 5 THEN D.Type
WHEN @SortOrder = 6 THEN CAST(E.fDateStart AS VARCHAR(50))
ELSE elementName
END
February 12, 2010 at 2:18 pm
I couldn't find an elegant way but here's what might work if you have to have a paramterized option for ASC/DESC:
DECLARE @SortOrder INT
SET @SortOrder=2
DECLARE @SortOrder2 INT
SET @SortOrder2=2
;WITH cte AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY
ORDER BY
CASE WHEN @SortOrder = 1 THEN eName
WHEN @SortOrder = 2 THEN eDisplay
WHEN @SortOrder = 3 THEN pName
WHEN @SortOrder = 4 THEN sLastName
WHEN @SortOrder = 5 THEN D.Type
WHEN @SortOrder = 6 THEN CAST(E.fDateStart AS VARCHAR(50))
ELSE elementName
END
) ROW,
YourCols
FROM YourTable
)
SELECT YourCols
FROM cte
ORDER BY
CASE WHEN @SortOrder2=1 THEN ROW ELSE -ROW END
February 15, 2010 at 8:12 am
I'm not sure if this would work...does this use ASC or DESC?
Thanks!
February 15, 2010 at 11:53 am
Magy (2/15/2010)
I'm not sure if this would work...does this use ASC or DESC?Thanks!
Yes and no.
No, since you won't find the keywords.
But yes, since this code's using a little trick:
I used the ROW_NUMBER() function to get the data sorted according to the order specified by @SortOrder. You could test the result by running the query that's inside the CTE.
The query outside the CTE will order the results of ROW_NUMBER() in ascending order when @SortOrder2=1, otherwise it will order by the negative value of row, which is equal to a descending order.
The easiest way to check the results is to set up some sample data and give it a try! 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply