select @variable=case when...end from... order by len(@variable)

Question

What I'm trying to do is concatenate two fields of a table, sort them by the descending length of the concatenated fields and get the top result...

Here's the SQL I'm using to try and achieve this...

declare @color nvarchar(255);
declare @board_name nvarchar(255);

select top(1)
    @board_name = board_name, 
    @color = base_color + case when shade_color is null then '' else '/' + shade_color end 
from 
    cut_vinyl
order by 
    len(@color) desc;

select @board_name, @color;

So, if I had the following table for cut_vinyl

board_name | base_color | shade_color
=====================================
board0001  | clear      | NULL
board0002  | clear      | blue
board0003  | bronze     | bronze
board0004  | bronze     | green
board0005  | bronze     | blue
board0006  | bronze     | NULL

Then I would expect @board_name to be board0003 and @color to be bronze/bronze, yet what I seem to be getting instead is a result like @board_name = board0001, and @color = clear

Answer 1

You're ordering on a variable:

order by 
    len(@color) desc;

If you're looking for the longest color, use:

order by 
    len(base_color) + len(shade_color) desc;

instead.