CASE is an expression - it returns a single scalar value (per row). It can't return a complex part of the parse tree of something else, like an ORDER BY clause of a SELECT statement.
It looks like you just need:
ORDER BY
CASE WHEN TblList.PinRequestCount <> 0 THEN TblList.PinRequestCount END desc,
CASE WHEN TblList.HighCallAlertCount <> 0 THEN TblList.HighCallAlertCount END desc,
Case WHEN TblList.HighAlertCount <> 0 THEN TblList.HighAlertCount END DESC,
CASE WHEN TblList.MediumCallAlertCount <> 0 THEN TblList.MediumCallAlertCount END DESC,
Case WHEN TblList.MediumAlertCount <> 0 THEN TblList.MediumAlertCount END DESC,
TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC
Or possibly:
ORDER BY
CASE
WHEN TblList.PinRequestCount <> 0 THEN TblList.PinRequestCount
WHEN TblList.HighCallAlertCount <> 0 THEN TblList.HighCallAlertCount
WHEN TblList.HighAlertCount <> 0 THEN TblList.HighAlertCount
WHEN TblList.MediumCallAlertCount <> 0 THEN TblList.MediumCallAlertCount
WHEN TblList.MediumAlertCount <> 0 THEN TblList.MediumAlertCount
END desc,
TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC
It's a little tricky to tell which of the above (or something else) is what you're looking for because you've a) not explained what actual sort order you're trying to achieve, and b) not supplied any sample data and expected results, from which we could attempt to deduce the actual sort order you're trying to achieve.
This may be the answer we're looking for:
ORDER BY
CASE
WHEN TblList.PinRequestCount <> 0 THEN 5
WHEN TblList.HighCallAlertCount <> 0 THEN 4
WHEN TblList.HighAlertCount <> 0 THEN 3
WHEN TblList.MediumCallAlertCount <> 0 THEN 2
WHEN TblList.MediumAlertCount <> 0 THEN 1
END desc,
CASE
WHEN TblList.PinRequestCount <> 0 THEN TblList.PinRequestCount
WHEN TblList.HighCallAlertCount <> 0 THEN TblList.HighCallAlertCount
WHEN TblList.HighAlertCount <> 0 THEN TblList.HighAlertCount
WHEN TblList.MediumCallAlertCount <> 0 THEN TblList.MediumCallAlertCount
WHEN TblList.MediumAlertCount <> 0 THEN TblList.MediumAlertCount
END desc,
TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC