ORDER BY取决于参数导致错误

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?

为什么它会尝试将某些东西转换为日期/时间。有人可以帮忙吗？

1 个解决方案

#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语句：

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.

问题是该案例具有单个输出类型，在编译查询时确定。此类型基于逻辑组合THEN子句的所有类型。因此，每个then子句的结果将转换为整体类型 - 这就是您的错误发生的地方。

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

您可以在此处阅读有关数据优先级规则。但解决方案很简单：使用多个case语句。

#1