I have a fairly simple pagination query used to get rows from a table
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
The @orderby parameter decides which column the results should be ordered by.
news_edit.[time] and news_edits.lastedit are both datetime fields. But news_edits.title is a varchar field.
The query runs fine for both the datetime fields but when @orderby = 2 I get the following error:
"Conversion failed when converting date and/or time from character string."
The problem I'm having is that I'm not trying to convert anything?
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
This is because single CASE statement requires that all branches have compatible data types. Since your character string in one CASE can't be converted to the date time returned from another CASE, you get the conversion error.
As you're not explicitly casting the "order by case..." values, SQL Server infers it's a datetime (according to the type of the first case).
The solution to your problem is to cast the dates in a string format that allows you to order by it, somewhat like 'yyyyMMddhhmmss'. If you do so, all the "order by case..." values will be chars and it will work.
Alternatively, you can have two selects, and choose one of them with an if. The first part of the if for the dates, the second for the title.