Search code examples
sqlsql-servert-sqlstored-procedures

Stored Procedure with composite Order by

I am creating a stored procedure like this :

select * 
from INVENTORYTABLE inv
join INVENTORYTABLE_New invNew on inv.Stock_ID = invNew.Stock_ID
where 
   (
   (AvailabilityStatus ='A' Or AvailabilityStatus = 'B' Or AvailabilityStatus = 'D' Or AvailabilityStatus = 'H' or AvailabilityStatus='E' ) And 
   MediaCount>0
   )

I want to sort the results by stockId for Availability status 'A' to 'H' and then for 'E'. Is this possible?

What I want is to show results from A to H ordered by StockId and then E below the ordered by stock Id.

Solution

  • You may implement an ORDER BY clause using a CASE expression:

    ORDER BY 
   CASE WHEN AvailabilityStatus = 'E' THEN 1 ELSE 0 END, 
   stockId