Applications that allow users to sort data by different columns of the table might need to go for dynamic stored procedures or will end up in adding multiple procedures based on the number of combinations. In SQL Server we can achieve this easily through parameterized sorting.
As shown in the below example we can have a parameter which says the column on which the sort criteria can be applied and using the case statement we achieve the results without going for a dynamic /multiple stored procedures.
DECLARE @SortOrder INT
SET @SortOrder =1
WHERE Forename LIKE 'A%'
ORDER BY CASE WHEN @SortOrder = 1 THEN ForeName
WHEN @SortOrder = 2 THEN SurName