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
	FROM dbo.Students
	ORDER BY CASE WHEN @SortValue=1 THEN FullName WHEN @SortValue=2 THEN RegNo ELSE StudentClass END

3 thoughts on “Dynamic ORDER BY in SQL Server

    • Yes, there is some performance issue with CASE ..WHEN.. But, I believe most of the times it depends on the situation, whether I should use it or not. Like in a situation where my query suppose to return very few records then I can bear the performance cost. While working on a web project, most of the time we need to show few records at a time in the browser as a user can read only the contents of one screen size. In those cases we can use it.

      If I have to avoid CASE.. WHEN.. then the query can also be written using IF..ELSE..
      If I am on some wrong way and you have any better idea then please share which will be really helpful for me.
      Thanks for your valuable feedback !

  1. Pingback: SQL Server: Custom Sorting « Suvendu's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s