Sometimes we need to sort the select list based on the field passed as parameter, which means while writing the query we are not sure to sort the select list based on which field. Then we just can let the query decide this at the time of execution. To do this we will use dynamic ORDER BY clause. If we employ this using stored procedure then our script will look like following
CREATE PROC [dbo].[spGetDynamicSortedStudents] ( @SortValue INT ) AS BEGIN SELECT * FROM dbo.Students ORDER BY CASE WHEN @SortValue=1 THEN FullName WHEN @SortValue=2 THEN RegNo ELSE StudentClass END END