Execute the following script in Query Editor to demonstrate a data-driven ORDER BY.

USE AdventureWorks
GO
DECLARE @SortBy CHAR(10), @SortByDirection CHAR(1)
SET @SortBy = 'City'
SET @SortByDirection = 'A' -- or D for descending
SELECT
AddressLine1,
City,
State=p.Name,
PostalCode
FROM Person.Address a
JOIN Person.StateProvince p
ON a.StateProvinceID=p.StateProvinceID
WHERE AddressLine1 like '%drive%'
ORDER BY
CASE WHEN @SortBy = 'Postal' AND @SortByDirection = 'D' THEN PostalCode END DESC,
CASE WHEN @SortBy = 'Postal' AND @SortByDirection = 'A' THEN PostalCode END,
CASE WHEN @SortBy = 'State' AND @SortByDirection = 'D' THEN p.[Name] END DESC,
CASE WHEN @SortBy = 'State' AND @SortByDirection = 'A' THEN p.[Name] END,
CASE WHEN @SortBy = 'City' AND @SortByDirection = 'D' THEN City END DESC,
CASE WHEN @SortBy = 'City' AND @SortByDirection = 'A' THEN City END
GO