ORDER BY depending on parameter results in error

Question

I have a stored procedure that initiates an order by depending on a parameter:

DROP PROCEDURE [dbo].[GetUsersByClusterAndUserName]
  GO
CREATE PROCEDURE [dbo].[GetUsersByClusterAndUserName]
    @SortField [nvarchar] (256) = 'UserName',
    @SortOrder [int] = 0
AS
    SELECT * FROM [User] 
    ORDER BY
    CASE WHEN @SortOrder = 0 THEN
        CASE 
            WHEN @SortField = 'UserName' THEN User_UserName 
            WHEN @SortField = 'LastLoginDate' THEN User_LastLoginDate 
            WHEN @SortField = 'CreationDate' THEN User_CreationDate END
    END ASC,    
    CASE WHEN @SortOrder = 1 THEN
        CASE
            WHEN @SortField = 'UserName' THEN User_UserName 
            WHEN @SortField = 'LastLoginDate' THEN User_LastLoginDate
            WHEN @SortField = 'CreationDate' THEN [User_CreationDate] END
    END DESC
RETURN 0
GO

However... If I call the procedure like this:

EXEC dbo.GetUsersByClusterAndUserName @SortOrder=1, @SortField='UserName'

I get the following error:

Msg 241, Level 16, State 1, Procedure GetUsersByClusterAndUserName, Line 7
Conversion failed when converting date and/or time from character string.

Why would it try to convert something to date/time. Can anyone please help?

Answer 1

The problem is probably type conversion from the case. When using order by in this way, then use multiple case statements:

ORDER BY (CASE WHEN @SortOrder = 0 AND @SortField = 'UserName' THEN User_UserName END),
         (CASE WHEN @SortOrder = 0 AND @SortField = 'User_LastLoginDate' THEN User_LastLoginDate END),
         (CASE WHEN @SortOrder = 0 AND @SortField = 'User_CreationDate' THEN User_CreationDate END),
         (CASE WHEN @SortOrder = 1 AND @SortField = 'UserName' THEN User_UserName END) DESC,
         (CASE WHEN @SortOrder = 1 AND @SortField = 'User_LastLoginDate' THEN User_LastLoginDate END) DESC,
         (CASE WHEN @SortOrder = 1 AND @SortField = 'User_CreationDate' THEN User_CreationDate END) DESC

The problem is that the case has a single output type, determined when the query is compiled. This type is based on logic combining all the types form the THEN clause. So, the result from each then clause is converted to the overall type -- and this is where your error occurs.

You can read about data precedence rules here. But the solution is simple: use multiple case statements.