Article Link.

i liked the articles and found them giving new insight into TSQL. i could appreciate it because in a recent project we had a similar requirement but had to make do with dynamic SQL.

looking forward to more such articles !

This technique is great. However, how would you handle dynamically generating the "asc" or "desc" keyword after the order by, since you cannot place two cases one after the other (at least I don't know how to do it; something like:

ORDER BY
CASE
WHEN @sortColumn = 'CustomerID' THEN CustomerID
WHEN @sortColumn = 'CompanyName' THEN CompanyName
ELSE ContactName
END

CASE
WHEN @sortOrder = 'asc' THEN asc
WHEN @sortOrder = 'desc' THEN desc
END


fails.

The only solution left looks like using dynamic SQL...

Note the code below. The CASE statment doesn't like the mixed data types. It seems to work with dates and integers, but throw in a character value and I get an error such as:

Server: Msg 241, Level 16, State 1, Line 8
Syntax error converting datetime from character string.

Any help there?


/*
create table asset (asset_id int, title varchar(255), start_date datetime)
*/

DECLARE @sortColumn VARCHAR(255)
SELECT @sortColumn = 'title'

SELECT * FROM asset
ORDER BY
CASE
WHEN @sortColumn = 'title' THEN title
WHEN @sortColumn = 'start_date' THEN start_date
WHEN @sortColumn = 'asset_id' THEN asset_id
ELSE title
END

This is a useful technique, but you left out one caveat:

Every possible result of the case expression needs to have the same datatype - otherwise when you want to optionally sort on an Int field or a Varchar field, you'll have problems, because SQL Server seems always to decide to convert all the expressions to a numeric datatype, and complains that the character column isn't numeric.

The way to get around this is to do an explicit type conversion, using the convert or cast functions to convert the THEN expressions to a common datatype (generally varchar).

eg:
...
ORDER BY CASE WHEN @var = 1 THEN VarcharCol
WHEN @var = 2 THEN CAST(NumericCol AS VARCHAR(50))
END

But what about order different data types such as varchar or integer?
I try it and it's not work. Exeptions occured when I order by ID and Description columns in my table.

Asc and Desc work correctly, but both datatypes... :-/

PS: I try to convert datatypes, but it longer not work.

I couldn't get the dynamic asc/desc to work, either.

So here's a workaround, when using a front-end like VB or ASP to display the data.

Perform the dynamic order by in the SQL, and bring the recordset into an array. With ADO, you can use GetRows.

Then, if the user wants Descending order, traverse the array backwards. Otherwise, traverse it forwards.