Pages

Sunday 16 September 2012

SQL SERVER - Dynamic sorting using CASE statement

Let us look at the different examples to sort a field dynamically based on parameter supplied by the user in SQL Server. We can implement this by using dynamic SQL where we create a query on the fly and execute it, but dynamic SQL has many issues and very few advantages. If we are willing to limit ourselves to a fixed number of columns to sort then it is possible to achieve without dynamic SQL otherwise completely dynamic sorting by using several CASE statements will be the solution. We can write these types of queries using CASE statement with ORDER BY clause.

Following examples will demonstrate to use several CASE statements in ORDER BY to sort:

-- -- First, create a new database and a table to test
GO
CREATE DATABASE MyTestDB
GO

USE MyTestDB
GO
CREATE TABLE dbo.Employees (
EmployeeID INT IDENTITY(1,1),
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)

INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
SELECT 'Barry','IT', 80000 UNION
SELECT 'Jones','IT', 40000 UNION
SELECT 'Billy','IT', 50000 UNION
SELECT 'Ralph','IT', 50000 UNION
SELECT 'Aaron','Finance', 40000 UNION
SELECT 'Barney','Finance', 25000 UNION
SELECT 'Callum','Finance', 25000 UNION
SELECT 'Oliver','Finance', 15000 UNION
SELECT 'Anderson','HR', 25000 UNION
SELECT 'Martin','HR', 15000 UNION
SELECT 'Garcia','HR', 15000 UNION
SELECT 'Caine','HR', 10000

Now create stored procedures to get sorted records from Employees table based on given field.

-- 1 -- Decide field to sort but without dynamic sort order.
CREATE PROCEDURE dbo.GetAllEmployees_1
(
@SortField VARCHAR(50) = 'EmployeeName'
)
AS
BEGIN

SELECT EmployeeName, Department, Salary
FROM dbo.Employees
ORDER BY
CASE
WHEN @SortField = 'EmployeeName' THEN EmployeeName
ELSE @SortField
END
ASC

END
GO

-- Execute the stored procedure created above:

EXEC dbo.GetAllEmployees_1 'EmployeeName'

-- 2 -- User option to sort on any field and sort in ascending or descending order method 1.
CREATE PROCEDURE dbo.GetAllEmployees_2
(
@SortField VARCHAR(50) = 'EmployeeName',
@SortDir VARCHAR(4) = 'ASC'
)
AS
BEGIN

SELECT EmployeeName, Department, Salary
FROM dbo.Employees
ORDER BY
CASE WHEN @SortField = 'EmployeeName' AND @SortDir = 'ASC' THEN EmployeeName END
ASC,
CASE WHEN @SortField = 'EmployeeName' AND @SortDir = 'DESC' THEN EmployeeName END
DESC,
CASE WHEN @SortField = 'Department' AND @SortDir = 'ASC' THEN Department END ASC,
CASE WHEN @SortField = 'Department' AND @SortDir = 'DESC' THEN Department END
DESC,
CASE WHEN @SortField = 'Salary' AND @SortDir = 'ASC' THEN Salary END ASC,
CASE WHEN @SortField = 'Salary' AND @SortDir = 'DESC' THEN Salary END DESC

END
GO

-- Execution of the following stored procedures will show the records sorted based on given field and based on given sort order:

EXEC dbo.GetAllEmployees_2 'EmployeeName', 'ASC'
EXEC dbo.GetAllEmployees_2 'EmployeeName', 'DESC'

-- 3 -- User option to sort on any field and sort in ascending or descending order method 2
CREATE PROCEDURE dbo.GetAllEmployees_3
(
@SortField VARCHAR(50) = 'EmployeeName',
@SortDir VARCHAR(4) = 'ASC'
)
AS
BEGIN

SELECT EmployeeName, Department, Salary
FROM dbo.Employees
ORDER BY
CASE WHEN @SortField = 'EmployeeName' AND @SortDir = 'ASC' THEN EmployeeName END
ASC,
CASE WHEN @SortField = 'EmployeeName' AND @SortDir = 'DESC' THEN EmployeeName END
DESC,
CASE WHEN @SortField = 'Department' AND @SortDir = 'ASC' THEN Department END ASC,
CASE WHEN @SortField = 'Department' AND @SortDir = 'DESC' THEN Department END
DESC,
CASE WHEN @SortField = 'Salary' AND @SortDir = 'ASC' THEN Salary END ASC,
CASE WHEN @SortField = 'Salary' AND @SortDir = 'DESC' THEN Salary END DESC

END
GO

-- Execute the following code to test the above created stored procedure:

EXEC dbo.GetAllEmployees_3 'EmployeeName', 'ASC'
EXEC dbo.GetAllEmployees_3 'EmployeeName', 'DESC'

Sometimes want to display records based on priority that also could done by dynamic sorting using CASE statement. In the following example we will sort the field Department based on priority where department “IT” should be first then “Finance” and then “HR”.

-- 4 -- Order by priority.
CREATE PROCEDURE dbo.GetAllEmployees_4
AS
BEGIN

SELECT EmployeeName, Department, Salary
FROM dbo.Employees
ORDER BY
CASE
WHEN Department = 'IT' THEN 1
WHEN Department = 'Finance' THEN 2
WHEN Department = 'HR' THEN 3
END ASC

END
GO

-- Execute the following code to test:
EXEC dbo.GetAllEmployees_4

In above example, the data type must be the same for sort field. In this example the data type is of integer. This can be helpful where in some situations we want display data based on priority.

No comments: