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
@orderby parameter decides which column the results should be ordered by.
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?
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.