sql orderby conversion error when case is used

I have the following query. When run I receive

Error converting data type nvarchar to numeric.

This only occurs when @sortorder = 1 or -1, sorted by name. However, if the case statement is removed and just order by Name asc is used the query run without error.

Could anyone explain whats going on here? thanks.

DECLARE @SortOrder INT = 1

CREATE TABLE #Results
(
ID INT IDENTITY(1,1),

NameID INT,

Expected NUMERIC(5,1),

Actual NUMERIC(5,1)

)

SELECT Name, Expected, Actual, ID 

FROM (

      SELECT ISNULL(NULLIF(Words.Word, ''), Name.Primary) AS [Name], #Results.Expected, #Results.Actual, #Results.NameID 
    FROM #Results
    INNER JOIN Name ON Name.ID  = #results.NameID 
    LEFT OUTER JOIN Words ON Name.WordID = Words.WordID 
        AND Words.LanguageID = 0
    UNION
    SELECT 'AVG' AS Name,
            CAST(((SUM(#Results.Expected))/COUNT(#Results.ID)) AS NUMERIC(5,1)) AS [Expected],
            CAST(((SUM(Actual))/COUNT(ID)) AS NUMERIC(5,1))AS [Actual],
            -9999 as [ID]
    FROM #Results 
) AS Results

ORDER BY 
    CASE @SortOrder

        WHEN 1 THEN Name    
        WHEN 2 THEN Expected
    开发者_如何学编程    WHEN 3 THEN Actual      
        END ASC,    
    CASE @SortOrder 
        WHEN -1 THEN Name   
        WHEN -2 THEN Expected
        WHEN -3 THEN Actual
        END DESC

You need a separate case expression for each potential data type.

ORDER BY 
    CASE @SortOrder
        WHEN 1 THEN Name END,
    CASE @SortOrder
        WHEN 2 THEN Expected
        WHEN 3 THEN Actual      
        END,    
    CASE @SortOrder 
        WHEN -1 THEN Name END DESC,
    CASE @SortOrder   
        WHEN -2 THEN Expected
        WHEN -3 THEN Actual
        END DESC;

As a general rule, don't ever use something like that. Use three separate queries:

if @SortOrder = 1 then
select ... order by Name;
else if @SortOrder = 2 then
select ... order by Expected
else if @SortOrder = 3 then
select ... order by Actual;

What you're doing now is the 100% sure way to deny the SQL Server query optimizer any decent chance to come up with an efficient plan: the generated plan has to work for any value of the @SortOrder variable, therefore it must do a sort even if an index would satisfy the order by constraint. For a more lengthier discussion on the topic read Dynamic Search Conditions in T-SQL.

Now in your case the data coming from a #temp table is even more important to separate the cases higher up the stack. Since #temp tables support indexes, add the appropriate clustered index to the #temp table itself when you create it. Which implies using specific, separate, #temp tables for Name, Expected or Actual order by requirement.

The CASE expression as a whole will be coerced to the data type of the branch with the highest precedence.

You could try casting to sql_variant as this has a higher precedence than everything except UDTs and should sort correctly.