我有一个相当简单的分页查询,用于从表中获取行
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@H_301_3@@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@H_301_3@这是因为单个CASE语句要求所有分支具有兼容的数据类型.由于您的一个CASE中的字符串不能转换为从另一个CASE返回的日期时间,所以您会收到转换错误.