存储过程中的动态ORDER BY没有按预期工作

Question

这是我搜索用户上传的图片库的存储过程:

ALTER PROCEDURE dbo.sp_SearchGallery
(
@strSearchTerm NVARCHAR(50) = NULL,
@strCategory NVARCHAR(50) = NULL,
@nUserId INT = NULL,
@nSortBy INT = 0,
@nSortDesc BIT = 0,
@nPage INT = 1,
@nPageSize INT = 10
) AS
SET NOCOUNT ON
DECLARE @nSortSwitch INT = 1
IF @nSortDesc = 1 BEGIN
    SET @nSortSwitch = -1
END
DECLARE @FirstRec INT = (@nPage - 1) * @nPageSize
DECLARE @LastRec INT = (@nPage * @nPageSize + 1)
; WITH rowQueryResults AS
(
    SELECT *, ROW_NUMBER() OVER
    (
        ORDER BY
            CASE @nSortBy
                WHEN 0 THEN Date
                WHEN 1 THEN Title
                WHEN 2 THEN Description
                WHEN 3 THEN ID
            END
    ) AS RowNum
    FROM dbo.GalleryItems
    WHERE ((@strSearchTerm IS NULL OR Title LIKE '%' + @strSearchTerm + '%') OR
          (@strSearchTerm IS NULL OR Description LIKE '%' + @strSearchTerm + '%')) AND
          (@nUserId IS NULL OR UserId = @nUserId) AND
          (@strCategory IS NULL OR Category LIKE '%' + @strCategory + '%') AND
          (Accepted=1)
)
SELECT *
FROM rowQueryResults
WHERE RowNum > @FirstRec AND RowNum < @LastRec
ORDER BY RowNum*@nSortSwitch
RETURN

我可以将@nSortBy设置为0，这很好。我可以将其设置为3，这也可以正常工作，但只要我将其设置为1或2，它就会崩溃，Conversion failed when converting date and/or time from character string.

除外

我假设的是它试图将Title或Description转换为DateTime进行排序。ID工作的原因是因为它是一个整数，所以可以安全地转换为日期时间(这不是我想要的，显然)

但是，它为什么要这样做呢?我如何让它在排序时使用字符串而不是时间比较?

Answer 1

这与数据优先级有关，当在不同的数据类型之间SQL将较低的数据类型转换为最高的数据类型时。试一下，这样你的最高数据类型就变成了varchar，这样它就不会尝试将Title和Description转换为datetime。

ORDER BY
        CASE @nSortBy
            WHEN 0 THEN convert(varchar, Date)
            WHEN 1 THEN Title
            WHEN 2 THEN Description
            WHEN 3 THEN convert(varchar, ID)
        END

https://msdn.microsoft.com/en-us/library/ms190309.aspx

Answer 2

有一个不需要转换的简单替代方法:

ORDER BY
        CASE WHEN @nSortBy = 0 THEN Date END,
        CASE WHEN @nSortBy = 1 THEN Title END,
        CASE WHEN @nSortBy = 2 THEN Description END,
        CASE WHEN @nSortBy = 3 THEN ID END

这是通过一个奇怪的小技巧实现的。理论上，它是在四个字段上按顺序进行ORDER BY。在实践中，虽然@nSortBy只能有一个值，所以实际上只作用于四个排序中的一个。