troubleshooting Question

How to use a COALESC within a CASE statement

Avatar of Lisa0440
Lisa0440 asked on
4 Comments1 Solution1479 ViewsLast Modified:
I'm trying to make my dynamic SQL sort work.  I'm sorting by 3 values but the second one's sort order (asc/desc) depends on an input parameter.  It all works except for when the second value's sort order is set to ascending the nulls are given first.  Is there any way to make the nulls appear at the end.  I've tried using a Case within the Case statement but I can't get that working.  Below is the sql I am working with.  The third case statement with the @Priority_Sort set to ascending is what is giving me the problem.

ORDER BY CASE WHEN sched.StartDate Is NULL Then 1 Else 0 End, sched.StartDate,
                                            CASE WHEN @Priority_Sort = 'Descending' Then CAST(COALESCE(sched.Priority, 0) AS INT) END DESC,
                                            CASE WHEN @Priority_Sort = 'Ascending' Then CAST(COALESCE(sched.Priority, 0) AS INT) END ASC,
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros