• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

T-SQL ORDER BY - CASE ... WHEN

can someone see the problem here? I am trying to have 2 sort orders by parameter (ASC and DESC) and the column name by parameter also... this query gives this error: Incorrect syntax near the keyword 'WHEN'.


declare 
	@SortColumn varchar(100)
	, @SortOrder varchar(5)

set @SortColumn = 'Title'
set @SortOrder = 'asc'

select 
	* 
from 
	Projects 
Order By 
	CASE @SortOrder 
            WHEN 'asc' THEN  
            CASE @SortColumn 
                WHEN 'Title' THEN Title 
		WHEN 'DateReleased' THEN DateReleased 
                END 
            END 
            WHEN 'desc' THEN  
            CASE @SortColumn 
                WHEN 'Title' THEN Title 
		WHEN 'DateReleased' THEN DateReleased 
                END 
            END 
	    desc

Open in new window

0
conrad2010
Asked:
conrad2010
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
By the way, the CASE WHEN is not complete, but even if it is it won't do what you are wanting. The DESC/ASC cannot be inside the CASE from my experience. You will likely have two sorts next to each other that only activate based on the @SortOrder.
0
 
Kevin CrossChief Technology OfficerCommented:
Here is an example of what I mean.
set @SortOrder = 'desc';

;with Projects(Title, DateReleased) as (
   select 'A title', dateadd(dd, +2, getdate()) union
   select 'B title', dateadd(dd, -1, getdate())
)
select 
	* 
from 
	Projects 
Order By 
CASE @SortOrder 
   WHEN 'asc' THEN 
      CASE @SortColumn 
         WHEN 'Title' THEN Title 
	 WHEN 'DateReleased' THEN CONVERT(VARCHAR, DateReleased, 120)
      -- note: this end closes inner CASE
      END
   -- note: this closes upper CASE
   -- so your original attempt closed CASE before second WHEN
   END, -- handle each sort separately
CASE @SortOrder 
   WHEN 'desc' THEN 
      CASE @SortColumn 
         WHEN 'Title' THEN Title 
         -- THEN values must be same data type
         WHEN 'DateReleased' THEN CONVERT(VARCHAR, DateReleased, 120)
      END
   END DESC
;

Open in new window

0
 
conrad2010Author Commented:
Much appreciated!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now