While some of the examples linked to surely work, their presentation is a bit messy.

I knocked up this example based on some kind of solution I half remembered:

DECLARE @sortfruit BIT, @sortamount BIT;

/*test cases*/

--DECLARE @sortfruit BIT = 1, @sortamount BIT;

--DECLARE @sortfruit BIT = 0, @sortamount BIT;

--DECLARE @sortfruit BIT, @sortamount BIT = 0;

--DECLARE @sortfruit BIT = 0, @sortamount BIT = 1;

WITH TestTable AS (

SELECT 1 AS Id, 'apple' AS Fruit, 12 AS Amount

UNION

SELECT 2 AS Id, 'banana' AS Fruit, 6 AS Amount

UNION

SELECT 3 AS Id, 'banana' AS Fruit, 4 AS Amount

UNION

SELECT 4 AS Id, 'cherry' AS Fruit, 25 AS Amount

)

SELECT *

FROM TestTable

ORDER BY CASE WHEN @sortfruit = 1 THEN Fruit END ASC

, CASE WHEN @sortfruit = 0 THEN Fruit END DESC

, CASE WHEN @sortamount = 1 THEN Amount END ASC

, CASE WHEN @sortamount = 0 THEN Amount END DESC;

which in production we would turn into a stored procedure. The parameters would be of BIT type, with NULL meaning no sort, 1 ascending, 0 descending.

This example is limited in the sense that you cannot change the column order though.