When attempting to Sort by different "MULTIPLE" Columns using a SQL Server Stored Procedure, this is the how it is done!
USE AdventureWorks;
SELECT ContactID,
FirstName,
LastName,
Title = COALESCE(Title,'')
FROM Person.Contact
WHERE LEFT(FirstName,1) = 'M'
ORDER BY CASE
WHEN LEFT(LastName,1) = 'A' THEN RANK() OVER(ORDER BY FirstName + LastName)
WHEN LEFT(LastName,1) = 'M' THEN RANK() OVER(ORDER BY LastName + ', ' + FirstName, Title)
WHEN LEFT(LastName,1) = 'U' THEN RANK() OVER(ORDER BY LastName + ', ' + FirstName DESC)
ELSE RANK() OVER(ORDER BY LastName DESC, FirstName DESC)
END
Another example: This is passing in the Sort order via a Stored Procedure Parameter. In this case @SortOrder is a varchar(100).
SELECT ItemId, Prod, Size, TagNo, ProductId, Location, PONo
FROM vewInvTrxSummary
ORDER BY
CASE WHEN @SortOrder LIKE '%Product, Tag, Location, Size, PO%'
THEN RANK() OVER(ORDER BY Prod, TagNo, Location, Size, PONo)
WHEN @SortOrder LIKE '%Product, Size, Tag, Location, PO%'
THEN RANK() OVER(ORDER BY Prod, Size, TagNo, Location, PONo)
WHEN @SortOrder LIKE '%Size, Product, Tag, Location, PO%'
THEN RANK() OVER(ORDER BY Size, Prod, TagNo, Location, PONo)
WHEN @SortOrder LIKE '%Product, PO, Tag%'
THEN RANK() OVER(ORDER BY Prod, PONo, TagNo)
ELSE RANK() OVER(ORDER BY Prod, TagNo, Location, Size, PONo)
END