Answered
CFCASE logic in SELECT statement
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!
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!