How To Implement Conditional Order By in SQL

Lately I have been working on a project where a client requested a data to be returned in a sorted order with the flexibility of being able to select a column which they wanted the data to be sorted by. This option, using pure vanilla T-SQL is rather complex to achieve using T-SQL as writing a stored procedure that, for example, underpins the SSRS report in the following fashion will not work.

CREATE PROCEDURE dbo.Sort_Dynamically
@SortColumn NVARCHAR (100),
@SortDirection VARCHAR (4)
AS
BEGIN
 ... ORDER BY @SortColumn, @SortDirection
END
GO

There are few different approaches that could provide the flexible sorting functionality, so let’s start with putting together a sample data set we can execute the SQL code against.

USE MASTER
GO
IF (EXISTS(SELECT Name FROM SysDatabases WHERE name = 'SortDB' ))
BEGIN
ALTER DATABASE [SortDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [SortDB]
END
GO
CREATE DATABASE SortDB
GO
USE SortDB
GO

IF EXISTS
	(SELECT  * FROM SortDB.dbo.sysobjects o
    WHERE o.xtype in ('U')
    and o.id = object_id(N'sortdb..temp_Table'))
DROP TABLE Temp_Table
GO

SELECT TOP 1000
key_col					= ROW_NUMBER() OVER (ORDER BY s1.[object_ID]),
object_id				= s1.[object_id],
name					= s1.name COLLATE DATABASE_DEFAULT,
object_tp_description	= s1.type_desc COLLATE DATABASE_DEFAULT,
modify_date				= s1.modify_date
INTO Temp_Table
FROM sys.all_objects AS s1
ORDER BY s1.[object_id];

Given that we now have five attributes to potentially order by, let’s assume that all these will participate in the user selection option. Additionally, the user will want to ensure that the sort order will be one of the inputs/parameters so all data can be sorted in either ascending or descending order. To accomplish this requirement I have two separate methods which can produce the required data sets – one using dynamic SQL and another using window function.

DYNAMIC SQL METHOD

CREATE PROCEDURE dbo.Sort_Dynamically_dSQL
@SortColumn NVARCHAR (128),
@SortDirection VARCHAR (4)
AS
BEGIN
	SET NOCOUNT ON;
	--ENSURE THAT CORRECT SORTING DIRECTION IS ENTERED
	IF UPPER(@SortDirection) NOT IN ('ASC', 'DESC')
		BEGIN
			RAISERROR('Invalid entry for @SortDirection: %s', 11, 1, @SortDirection);
			RETURN -1;
		END
	--ENSURE THAT CORRECT COLUMN NAME IS ENTERED
	IF LOWER(@SortColumn) NOT IN ('key_col', 'object_id', 'name', 'object_tp_description', 'modify_date')
		BEGIN
			RAISERROR ('Invalid entry for @SortColumn: %s', 11, 1, @SortColumn);
			RETURN -1;
		END
	SET @SortColumn = QUOTENAME(@SortColumn);

	DECLARE @Sql NVARCHAR (MAX)
	SET @Sql = 'SELECT key_col, object_id, name, object_tp_description, modify_date
	FROM Temp_Table
	ORDER BY' + @SortColumn + '' + @SortDirection + ';'
	EXEC sp_executesql @Sql
END

This dynamic SQL encapsulated in a stored procedure with a few validation lines is pretty self-explanatory. It dynamically creates a SELECT statement based on the two variables entered as per below. Window function method, again encapsulated in a stored procedure, uses a different approach where sorting is done by means of ROW_NUMBER() function, whereas sort direction is the result of a simple multiplication to determine if the order should be ascending or descending as per the code below.

WINDOW FUNCTION METHOD

CREATE PROCEDURE dbo.Sort_Dynamically_RowNumber
@SortColumn NVARCHAR (128),
@SortDirection VARCHAR (4)
AS
BEGIN
	SET NOCOUNT ON;
	WITH x AS
		(SELECT key_col, object_id, name, object_tp_description, modify_date,
		rn = ROW_NUMBER() OVER(ORDER BY CASE @SortColumn
		WHEN 'key_col' THEN RIGHT ('000000000000' + RTRIM (key_col),12)
		WHEN 'object_id' THEN RIGHT(COALESCE(NULLIF(LEFT(RTRIM(object_id),1),'-'),'0')
		+ REPLICATE ('0',23) + RTRIM(object_ID),24)
		WHEN 'key_col' THEN RIGHT (RTRIM (key_col),12)
		WHEN 'object_id' THEN RIGHT(RTRIM(object_ID),24)
		WHEN 'name' THEN name
		WHEN 'object_tp_description' THEN object_tp_description
		WHEN 'modify_date' THEN CONVERT (VARCHAR(25), modify_date, 120)
		END) * CASE @SortDirection WHEN 'ASC' THEN 1 ELSE -1 END
		FROM Temp_Table)
	SELECT key_col, object_id, name, object_tp_description, modify_date
	FROM x
	ORDER BY rn
END

Now we can execute any of the two stored procedures with parameters assigned as per the column we wish to sort by and the sort order i.e. ascending or descending.

--SORT BY key_col column IN DESCENDING ORDER
--USING dbo.Sort_Dynamically_RowNumber STORED PROCEDURE
EXEC dbo.Sort_Dynamically_RowNumber 'key_col', 'desc'

--SORT BY modify_date column IN ASCENDING ORDER
--USING dbo.Sort_Dynamically_dSQL STORED PROCEDURE
EXEC dbo.Sort_Dynamically_dSQL 'modify_date', 'asc';

Submit Article :- BlinkList + Blogmarks + Digg + Del.icio.us + Ekstreme Socializer + Feedmarker + Furl + Google Bookmarks + ma.gnolia + Netvouz + RawSugar + Reddit + Scuttle + Shadows + Simpy + Spurl + Technorati + Unalog + Wink

http://scuttle.org/bookmarks.php/pass?action=add

Posted in: How To's, SQL, Uncategorized

Tags: ,

This entry was posted on Tuesday, August 27th, 2013 at 1:18 pm and is filed under How To's, SQL, Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply