Dynamic ORDER BY in SQL Server


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
Advertisements