I cannot agree to that and in my opinion there is just one correct answer to this: it depends!

What do you call an "incredibly simple system"? How do you call other systems? And how do know all the background about the one requirment of some special sorting?

The way I have shown works perfekt for some requirements, e.g. sorting the items for cash desk accountings in meaningful way that is standard for all 400 shops.

On the other hand I have never seen a website where a user can define how single values have to be sorted. I only have seen the possibility of giving a column name for sorting, ascending or descending.

The disadvantages using the cross-apply-method I see are performance and maintenance:

If the number of rows of the outer select increases the query will slow down as the apply-operator does the subselect for any outer row.

And what do you do when you have to change the sort logic or if there is a new statusid? In how many procedures will you have to change your code? Will you even find them all? A simple update of 1 table with a column for sort order seems to be much better.

But as I said: it depends.

Giving the user the possibility to choose a column for sorting or even let him decide, which values within a column are sorted as the user wants might be difficult. I wonder if this would even be part of the database procedure at all. On huge systems there might be thougts like "how many execution plan for one query do I want in my procedure cache?" or "will my execution plans be cached and reused?" Maybe it's more efficient doing the query without sorting on database level and sort the data in the frontend-programming.

Yes, there are many aspects. Depending on the background of the requirement you will have to find an appropriate solution.