SQL Server ERROR in SQL order by with case when expression

uxh89sit 于 2023-06-28 发布在其他

关注(0)|答案(1)|浏览(99)

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

sql-server

来源：https://stackoverflow.com/questions/76462429/error-in-sql-order-by-with-case-when-expression

1条答案

按热度按时间

beq87vna1#

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.

赞(0）回复(0）举报 2023-06-28

我来回答

SQL Server ERROR in SQL order by with case when expression

1条答案

相关问题

热门标签

最新问答