Here's a thought I had a couple of days ago. I actually was almost done with this blog entry yesterday, but ended up closing the window and not saving any of it. Argh.
My thought was on sql sorting. There are times when you want to have your results sorted at the server based on a parameter. Those of you who like dynamic sql would be all over that. It's so simple (in Northwind):
declare @SortType int
declare @sql varchar(2000)
-- SortTypes
-- 1- Lastname
-- 2- Firstname
-- 3- Employeeed
-- 4- Extension
-- 5- postalcode
set @sorttype=4
set @sql='select * from employees'
if @sorttype=1 set @sql=@sql + ' order by lastname'
if @sorttype=2 set @sql=@sql + ' order by firstname'
if @sorttype=3 set @sql=@sql + ' order by employeeid'
if @sorttype=4 set @sql=@sql + ' order by extension'
if @sorttype=5 set @sql=@sql + ' order by postalcode'
exec(@sql)
Ok. But as I've said before, I am not a fan of dynamic sql. And my environment doesn't allow it because all security is done through the stored procedures. So, how do we do the same thing without dynamic sql? It's a great use of the CASE function.
Let's start with the first two sort types: lastname and firstname. you can put the CASE in the ORDER BY section:
select *
from employees
order by case @sorttype
when 1 then lastname
when 2 then firstname
end
That was simple. So let's add the third type, employeeid.
order by case @sorttype
when 1 then lastname
when 2 then firstname
when 3 then employeeid
end
Works well. Wait, no it doesn't. Sorting by type 3 is fine, but sorting bu type 1 or 2 gives:
Msg 245, Level 16, State 1, Line 12
Conversion failed when converting the nvarchar value 'Davolio' to data type int.
Ok, that's not a problem, we'll just convert everything to varchar. So now it's like:
order by case @sorttype
when 1 then lastname
when 2 then firstname
when 3 then cast(employeeid as varchar)
end
That works, kind of. You won't notice the bug on Northwind, but you will when we do the next type, extension. We need to convert it to varchar also.
order by case @sorttype
when 1 then lastname
when 2 then firstname
when 3 then cast(employeeid as varchar)
when 4 then cast(extension as varchar)
end
Wow, that's not right. 2344 doesn't come before 428. But it does in the string world, which you forced your way into. There is a solution. we just need to pad the numbers with zeros. But how many zeros? All the values have to be the same length to be considered equivalent. so now we have:
order by case @sorttype
when 1 then lastname
when 2 then firstname
when 3 then cast(employeeid as varchar)
when 4 then replicate('0',10-len(extension))+ extension
end
That works fine. And we'll have the same problem with postalcode, which is numeric and character, so we need to pad the beginning of those to be equal-length strings. The final version:
select *
from employees
order by case @sorttype
when 1 then lastname
when 2 then firstname
when 3 then cast(employeeid as varchar)
when 4 then replicate('0',10-len(extension))+ extension
when 5 then replicate('0',20-len(postalcode))+ postalcode
end
You'd want to make the length of the postalcode be the max length of the field, in case it's blank. But the point is CASE is a very powerful tool. It can be used anywhere a single value can be used. In ORDER BY, in JOIN, in SELECT, it's universal. Now I'm going to save this before I lose it again.