Using a case statement in an ORDER BY clause:

Did you know that an ORDER BY clause can contain a case statement? Consider the following:

USE Northwind

DECLARE @OrderFlag INT
SET @OrderFlag = 2

SELECT * FROM Products
ORDER BY CASE @OrderFlag WHEN 1 THEN ProductID WHEN 2 THEN SupplierID END

We can do something like this in a stored procedure, and pass in a variable that determines which column to sort on. All is good, until we try this:

SELECT * FROM Products
ORDER BY CASE @OrderFlag WHEN 1 THEN ProductName WHEN 2 THEN UnitPrice END

This very similar statement gives us the following error:

Server: Msg 257, Level 16, State 51, Line 7
Implicit conversion from data type nvarchar to money is not allowed. 
Use the CONVERT function to run this query.

Conversion? Where? This little issue threw me for a loop once, and I recently helped a coworker with it, so I thought I would share. The trick here is that you can't mix types in the case statement. Something needs to be converted. We can't make the string a number, so we'll convert the number and sort it alphabetically.

We'll need to pad that number so that it will sort correctly, and we'll need to make sure the same number of decimal places is used for each row. I've included the sort expression in the select so we can see what it's doing.

SELECT STR(UnitPrice, 10, 2), * FROM Products ORDER BY CASE @OrderFlag 
WHEN 1 THEN ProductName 
WHEN 2 THEN STR(UnitPrice, 10, 2) END

So Jim, why would I want to do this?

I'm glad you asked. We all know that building a SQL query by concatenating strings is, well, inelegant. Many a well-meaning programmer has resorting to doing this because they needed to decide at runtime which column to sort on. With this technique, you can.

Testing to see if I can host this blog on my Brinkster account.

And some testing of formatting:

using System;
using System.ComponentModel;
using System.Diagnostics;
using System.Web;

...with trailing text