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,