Alternate syntax for order by in SQL Server

The order by clause is very flexible in SQL Server. You can pass it nearly anything and it will work.

In this section I show how to sort a results based on a runtime value. This works well for a stored procedure that is used for sorting and the user interface has the ability to sort by different columns. Instead of using Dynamic SQL, creating multiple stored procedures, or have a long list of if-else and then copy and pasting the same basic code (just changing the order by column), this solution is simple and easily maintainable.

Declare @Orderby as varchar(20)
Set @Orderby = 'CREATED DESC'
SELECT [NAME] as FullName, SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY -- add columns to sort by here
 CASE @Orderby WHEN 'NAME DESC' THEN [NAME] END DESC,
 CASE @Orderby WHEN 'NAME ASC' THEN [NAME] END ASC,
 CASE @Orderby WHEN 'SEARCH_CODE DESC' THEN SEARCH_CODE END DESC,
 CASE @Orderby WHEN 'SEARCH_CODE ASC' THEN SEARCH_CODE END ASC,
 CASE @Orderby WHEN 'CREATED DESC' THEN CREATED END DESC,
 CASE @Orderby WHEN 'CREATED ASC' THEN CREATED END ASC

You can refer to columns by number instead of column name. This can make columns that have complex subqueries easier to reference in the order by.

SELECT [NAME] as FullName, SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY 3 DESC, 1 ASC

Instead of column name or column number, you can use the column alias

SELECT [NAME] as FullName, SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY FullName ASC

Here are some variations on the previous example

SELECT [NAME] as 'FullName', SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY FullName ASC

SELECT [NAME] as 'FullName', SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY 'FullName' ASC

SELECT [NAME] 'FullName', SEARCH_CODE, CREATED
 FROM ALL_PERSON e
ORDER BY 'FullName' ASC