ERROR in SQL order by with case when expression

Question

I prepare a SQL query and I use a case-when expression for ordering purposes. If I used req_amount or apply_date as sort_column it works fine. But I use emp_name for sorting, I get this error

Conversion failed when converting date and/or time from character string.

Please help me to resolve this.

DECLARE @srch_text varchar(50) = 'Cha'
DECLARE @page_no int = 1
DECLARE @page_size int = 10
DECLARE @sort_column  varchar(50) = 'emp_name'
DECLARE @order varchar(50) = 'ASC'

if (@order  = 'asc' OR @order = 'ASC')
BEGIN
select i.TRN_ID,e.EMP_FULLNAME,i.REQUESTING_AMOUNT,i.BET_APPLY_DATE 
from INTERMEDIATE i
INNER JOIN EMPLOYEE e ON i.BET_EMP_NUMBER = e.EMP_NUMBER 
where e.EMP_FULLNAME LIKE '%'+@srch_text+'%'
ORDER BY 
CASE @sort_column
    WHEN 'req_amount' THEN i.REQUESTING_AMOUNT
    WHEN 'emp_name' THEN  e.EMP_FULLNAME
    WHEN 'apply_date' THEN i.BET_APPLY_DATE
    END   asc 
OFFSET @page_size * ((@page_no - 1)) ROWS FETCH NEXT @page_size ROWS ONLY
END
ELSE
BEGIN
select i.TRN_ID,e.EMP_FULLNAME,i.REQUESTING_AMOUNT,i.BET_APPLY_DATE 
from INTERMEDIATE i
INNER JOIN EMPLOYEE e ON i.BET_EMP_NUMBER = e.EMP_NUMBER 
where e.EMP_FULLNAME LIKE '%'+@srch_text+'%'
ORDER BY 
CASE @sort_column
    WHEN 'req_amount' THEN i.REQUESTING_AMOUNT
    WHEN 'emp_name' THEN  e.EMP_FULLNAME
    WHEN 'apply_date' THEN i.BET_APPLY_DATE
    END desc 
OFFSET @page_size * ((@page_no - 1)) ROWS FETCH NEXT @page_size ROWS ONLY
END

If you use CASE then you must use **explicit** convertion to **the same** (compatible) datatype for all WHEN/ELSE options. — Akina, Jun 13 '23 at 07:10

score 1 · Accepted Answer · answered Jun 13 '23 at 09:52

You can use separate ORDER BY expressions.

SELECT
  i.TRN_ID,
  e.EMP_FULLNAME,
  i.REQUESTING_AMOUNT,
  i.BET_APPLY_DATE 
FROM INTERMEDIATE i
INNER JOIN EMPLOYEE e ON i.BET_EMP_NUMBER = e.EMP_NUMBER 
WHERE e.EMP_FULLNAME LIKE '%' + @srch_text + '%'
ORDER BY 
  CASE WHEN @order = 'ASC' AND @sort_column = 'req_amount'  THEN i.REQUESTING_AMOUNT END,
  CASE WHEN @order = 'ASC' AND @sort_column = 'emp_name'    THEN e.EMP_FULLNAME END,
  CASE WHEN @order = 'ASC' AND @sort_column = 'apply_date'  THEN i.BET_APPLY_DATE END,
  CASE WHEN @order <> 'ASC' AND @sort_column = 'req_amount' THEN i.REQUESTING_AMOUNT END,
  CASE WHEN @order <> 'ASC' AND @sort_column = 'emp_name'   THEN e.EMP_FULLNAME END,
  CASE WHEN @order <> 'ASC' AND @sort_column = 'apply_date' THEN i.BET_APPLY_DATE END
OFFSET @page_size * ((@page_no - 1)) ROWS
FETCH NEXT @page_size ROWS ONLY;

Hvaing said that, it's probably better to just use dynamic SQL, as this is basically a Kitchen Sink Query. This means that indexes will get used properly (although the wildcard search is always going to be a problem).

Note the use of sp_executesql to pass parameters in.

DECLARE @sql nvarchar(max) = '
SELECT
  i.TRN_ID,
  e.EMP_FULLNAME,
  i.REQUESTING_AMOUNT,
  i.BET_APPLY_DATE 
FROM INTERMEDIATE i
INNER JOIN EMPLOYEE e ON i.BET_EMP_NUMBER = e.EMP_NUMBER 
WHERE e.EMP_FULLNAME LIKE ''%'' + @srch_text + ''%''
ORDER BY
  ' +

  CASE @sort_column
  WHEN 'req_amount' THEN 'i.REQUESTING_AMOUNT'
  WHEN 'emp_name'   THEN 'e.EMP_FULLNAME'
  WHEN 'apply_date' THEN 'i.BET_APPLY_DATE'
  ELSE '1'
  END +

  IIF(@order = 'ASC', ' ASC', ' DESC ') + '
OFFSET @page_size * ((@page_no - 1)) ROWS
FETCH NEXT @page_size ROWS ONLY;
';

PRINT @sql;  -- your friend

EXEC sp_executesql @sql,
  N'@srch_text varchar(200),
    @page_size bigint,
    @page_no bigint',
  @srch_text = @srch_text
  @page_size = @page_size,
  @page_no = @page_no;

You might want to take a read of Is there any better option to apply pagination without applying OFFSET in SQL Server? which explains why OFFSET FETCH is slow in general, and what to do instead.

ERROR in SQL order by with case when expression

1 Answers1