我有一个相当简单的分页查询,用于从表中获取行
ALTER PROCEDURE mytable.[news_editor_paginate]
@count int,@start int,@orderby int
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (@count) * FROM
(
SELECT news_edits.*,ROW_NUMBER() OVER (
ORDER BY CASE
WHEN @orderby = 0 THEN news_edits.[time]
WHEN @orderby = 1 THEN news_edits.lastedit
WHEN @orderby = 2 THEN news_edits.title
END
DESC
) AS num
FROM news_edits
) AS a
WHERE num > @start
END
@orderby参数决定结果应由哪个列排序.
news_edit.[time]和news_edits.lastedit都是datetime字段.但是news_edits.title是一个varchar字段.
查询对于datetime字段运行正常,但是当@orderby = 2时,我得到以下错误：
“转换日期和/或时间从字符串转换失败”.
我遇到的问题是我不想转换任何东西？
解决方法
You’ll need to divide your
ORDER BY into multiple CASE statements:
ORDER BY
CASE WHEN @orderby = 0 THEN news_edits.[time] END DESC,CASE WHEN @orderby = 1 THEN news_edits.lastedit END DESC,CASE WHEN @orderby = 2 THEN news_edits.title END DESC
这是因为单个CASE语句要求所有分支具有兼容的数据类型.由于您的一个CASE中的字符串不能转换为从另一个CASE返回的日期时间,所以您会收到转换错误.
