SQL Server 动态排序依据 [英] SQL Server Dynamic Order By

问题描述

我正在尝试在存储过程中使用动态顺序，以便我可以传递我希望将数据作为参数返回到存储过程中的顺序.这适用于 VARCHAR 字段，但是如果我尝试对 int 或 datetime 字段进行排序，则会出错，我的代码如下

I'm trying to use a dynamic order by in a stored procedure so I can pass the order I want the data returned into the stored procedure as a parameter. This works fine for VARCHAR fields however if I try to sort an int or datetime field it errors the code I have is as follows

DECLARE @ORDERBY INT
SET @ORDERBY = 1
SELECT TOP 10 * FROM TBL_LMS_USERS_RECORDs_LAST_ATTEMPT
ORDER BY 
CASE 
    WHEN @OrderBy = 1 THEN s10_record_dow
    --WHEN @OrderBy = 2 THEN pk_big_record_id
    else s10_record_dow
END

如果我取消对 case 语句中的第二个 WHEN 的注释，它会出错

If I uncomment the second WHEN in the case statement it errors with

将数据类型 varchar 转换为 bigint 时出错."

"Error converting data type varchar to bigint."

如果我不使用 case 语句，我可以按此字段进行排序.

I can order by this field fine if I dont use the case statement.

有什么想法吗?

推荐答案

改成这样:

SELECT TOP 10 * FROM TBL_LMS_USERS_RECORDs_LAST_ATTEMPT
ORDER BY 
    CASE WHEN @OrderBy = 1 THEN s10_record_dow ELSE NUll END,  
    CASE WHEN @OrderBy = 2 THEN pk_big_record_id ELSE NULL END,
    CASE WHEN @OrderBy <> 1 AND  @OrderBy <> 2 THEN s10_record_dow 
         ELSE NULL 
    END

这篇关于SQL Server 动态排序依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！