Using 2 order by in procedure
Your use of ORDER BY is correct. However, You need to know that it has to go in order by how you specified! So your query will first order sd.Status FIRST, then it will order by distance SECOND.

For example This is how you're expecting it to look (Just my random data I gave)

 sd.Status   distance
 ---------   --------
     A         231
     A         245
     A         657
     A         987
     B         123
     B         432
     D         465
     D         576
     S         555


Note how it is ORDERED.