Skip to main content
Inspiring
May 2, 2007
Answered

CFCASE logic in SELECT statement

  • May 2, 2007
  • 2 replies
  • 427 views
I am trying to set the sort column and sort direction based on a parameter and I am having difficulty doing both. I can do the sort order, but not the direction. Here is what I have:

SELECT *
FROM tblUsers AS o
JOIN #AllUsers t
ON o.intUserID = t.intUserID
ORDER BY
CASE
WHEN @11516412_sort = 'txtLastname' THEN tblUsers.txtLastname
WHEN @11516412_sort = 'txtEmail' THEN tblUsers.txtEmail
WHEN @11516412_sort = 'txtOrganization' THEN tblUsers.txtOrganization
ELSE txtLastname
END
CASE
WHEN @11516412_sortdir = 'ASC' THEN ASC
ELSE DESC
END


This works if I get rid of the second CASE statement, but I want to be able to pass in both the sort order and the sort direction. Any ideas?

Thanks!
This topic has been closed for replies.
Correct answer fillae
The solution I found is sort of cumbersome, but should work. Rather than explain, here is a link to another forum thread (SQLTeam).

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5942&whichpage=1

2 replies

fillaeAuthorCorrect answer
Inspiring
May 2, 2007
The solution I found is sort of cumbersome, but should work. Rather than explain, here is a link to another forum thread (SQLTeam).

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5942&whichpage=1
Inspiring
May 2, 2007
Maybe nest CASES in each arg_sort evaluation. Failing that, maybe dynamic SQL?