Using CASE in Order By Clause

I have the following SQL:

ALTER PROCEDURE [dbo].[SP_Products_GetList]


@Cat开发者_运维技巧ID int,
@CatName int,
@SortBy varchar(50),
@SortType varchar(50)

AS


SELECT Products.ProductID, ProductName, MAX(Price) Price FROM Products 
    INNER JOIN  ProductCategory
        on Products.ProductID = ProductCategory.ProductID 
    INNER JOIN  (
                    SELECT * FROM Categories 
                        WHERE 
                            ( @CatID is null or @CatID = CatID ) and
                            ( @CatName is null or @CatName = CatName )
                ) Categories 
        on ProductCategory.CatID = Categories.CatID 
    INNER JOIN ( 
                    SELECT ProductID, max(Price) Price  from Prices WHERE PriceID IN 
                            ( SELECT MAX(PriceID) FROM Prices 
                            GROUP BY ProductID , SizeID)
                    GROUP BY ProductID
                ) as Prices 
        on Prices.ProductID = Products.ProductID 
GROUP BY ProductName, CatName, Products.ProductID, Price
ORDER BY 
CASE @SortType 
    WHEN 'desc' THEN  
    CASE @SortBy 
        WHEN 'ProductID' THEN Products.ProductID 
        WHEN 'ProductName' THEN ProductName
        WHEN 'Price' THEN Price  
        END 
    END 

EXECUTION PASSS

EXEC    [dbo].[SP_Products_GetList]
        @CatID = 1,
        @CatName = NULL,
        @SortType = 'DESC',
        @Sortby = 'ProductID'

EXECUTION FAILED

EXEC    [dbo].[SP_Products_GetList]
        @CatID = 1,
        @CatName = NULL,
        @SortType = 'DESC',
        @Sortby = 'ProductName'

Msg 245, Level 16, State 1, Procedure SP_Products_GetList, Line 13 Conversion failed when converting the varchar value '01-My First Tools Diaper Cake' to data type int.

When I alter my query without case and write simple:

.....
ORDER BY ProductName

It works fine

Why is is trying convert varchar to int as shown in the error message?

Your case statement's output type is an int, as that's the type of the first element of the expression (Products.ProductID). In order for it to work, you'll have to explicitly convert each value to a varchar (meaning that you'll also have to prefix your values with zeroes in order for them to sort correctly).

You'd do better doing something like this:

ORDER BY 
    CASE WHEN @SortBy = 'ProductID' THEN Products.ProductID ELSE NULL END,
    CASE WHEN @SortBy = 'ProductName' THEN ProductName ELSE NULL END,
    CASE WHEN @SortBy = 'Price' THEN Price ELSE NULL END

Obviously, this doesn't take direction (ASC vs DESC) into account, but that should be straightforward to add.

You need to separate the case statements by type. For instance, have a case statement for your int columns. Have a 2nd case statement for your varchar types. You have ProductID listed first inside the case statement, which is an int, so it will use that as the data type for the case statement.

When you use a CASE statement, the output has to be a value/column of a particular type. In the case of Price, this is a most likely not a VARCHAR data type.

There are a few ways to do this effectively.

My personal favorite is to take the re-usable part of the query above the order by and put it into an inline table-valued function.

Then in your SP:

IF (@OrderBy = 'COL1')
    SELECT *
    FROM UDF(params)
    ORDER BY COL1
ELSE IF (@OrderBy = 'COL2')
    SELECT *
    FROM UDF(params)
    ORDER BY COL2

This is going to have the most efficient execution plan (actually it's going to have a very efficient conditional plan) since it's not trying to convert every column or do fancy workarounds like sorting on columns with no data in some cases.

And it's relatively modular in terms of the complex code which you don't want to have to repeat.

The case statement resolves to INT at this time because the ProductId returns an INT. You can rewrite this to cast it to VARCHAR to avoid the issue.

ORDER BY  
CASE @SortType  
    WHEN 'desc' THEN   
    CASE @SortBy  
        WHEN 'ProductID' THEN CONVERT(VARCHAR(MAX), Products.ProductID)
        WHEN 'ProductName' THEN CONVERT(VARCHAR(MAX), ProductName)
        WHEN 'Price' THEN CONVERT(VARCHAR(MAX), Price)
        END  
    END  

I used VARCHAR(MAX) only because I don't know the length of the ProductName field. You can alter this so that it is the length of the ProductName field.

What you appear to be trying to do is dynamically generate SQL on the fly. T-SQL isn't so great at this. The most efficient way to do this from a performance perspective is to build your query string and then execute it. This is a good technique in some situations, but your code is adequate as is. Here's an example of your code if it were dynamically generated SQL:

DECLARE @SqlString NVARCHAR(MAX) = '';
SET @SqlString = N'SELECT Products.ProductID, ProductName, MAX(Price) Price FROM Products  
    INNER JOIN  ProductCategory 
        on Products.ProductID = ProductCategory.ProductID  
    INNER JOIN  ( 
                    SELECT * FROM Categories  
                        WHERE  
                            ( @CatID is null or @CatID = CatID ) and 
                            ( @CatName is null or @CatName = CatName ) 
                ) Categories  
        on ProductCategory.CatID = Categories.CatID  
    INNER JOIN (  
                    SELECT ProductID, max(Price) Price  from Prices WHERE PriceID IN  
                            ( SELECT MAX(PriceID) FROM Prices  
                            GROUP BY ProductID , SizeID) 
                    GROUP BY ProductID 
                ) as Prices  
        on Prices.ProductID = Products.ProductID  
GROUP BY ProductName, CatName, Products.ProductID, Price 
ORDER BY  ' + @SortBy + ' ' + @SortType;

EXEC sp_ExecuteSQL @SqlString;

The above code isn't completely correct. The parameterized section in the middle probably should be reworked or you will need to pass the values as parameters to the sp_executesql command. There are two draw backs to using dynamically generated SQL. 1st, the code may be subject to SQL injection attacks. If this is an internal report, then it doesn't seem likely that this would be an issue. The 2nd drawback is that it is harder to understand. Just look at the code to see what I mean!