SQL使用CASE WHEN排序选emp_name时出现日期转换错误求助

错误原因 #

问题出在CASE表达式的类型一致性上。SQL要求CASE所有分支返回的数据类型必须统一，你的CASE里同时返回数值型（REQUESTING_AMOUNT）、字符串型（EMP_FULLNAME）、日期型（BET_APPLY_DATE）三种类型，SQL会自动按类型优先级（日期>数值>字符串）做隐式转换。当选择emp_name作为排序字段时，SQL尝试把字符串转成日期类型，就触发了Conversion failed when converting date and/or time from character string错误。

解决方案 #

方案1：拆分CASE表达式，确保类型一致 #

将ORDER BY子句拆分为多个CASE，每个CASE仅对应一个排序字段，保证每个分支返回的类型与对应字段一致，避免强制转换。同时合并重复的查询逻辑，简化代码：

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'

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 @sort_column = 'req_amount' AND @order = 'ASC' THEN i.REQUESTING_AMOUNT END ASC,
    CASE WHEN @sort_column = 'req_amount' AND @order = 'DESC' THEN i.REQUESTING_AMOUNT END DESC,
    CASE WHEN @sort_column = 'emp_name' AND @order = 'ASC' THEN e.EMP_FULLNAME END ASC,
    CASE WHEN @sort_column = 'emp_name' AND @order = 'DESC' THEN e.EMP_FULLNAME END DESC,
    CASE WHEN @sort_column = 'apply_date' AND @order = 'ASC' THEN i.BET_APPLY_DATE END ASC,
    CASE WHEN @sort_column = 'apply_date' AND @order = 'DESC' THEN i.BET_APPLY_DATE END DESC
OFFSET @page_size * (@page_no - 1) ROWS 
FETCH NEXT @page_size ROWS ONLY

方案2：使用动态SQL（需防范注入风险） #

如果觉得拆分CASE过于繁琐，可以用动态SQL拼接排序逻辑，通过参数化查询避免SQL注入：

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'
DECLARE @sql nvarchar(max)
DECLARE @params nvarchar(max)

-- 验证排序字段和方向的合法性，防止恶意注入
IF @sort_column NOT IN ('req_amount', 'emp_name', 'apply_date')
    SET @sort_column = 'emp_name'
IF @order NOT IN ('ASC', 'DESC')
    SET @order = 'ASC'

-- 映射前端字段名到实际表列
DECLARE @sort_col_real varchar(50)
SELECT @sort_col_real = CASE 
    WHEN @sort_column = 'req_amount' THEN 'i.REQUESTING_AMOUNT'
    WHEN @sort_column = 'emp_name' THEN 'e.EMP_FULLNAME'
    WHEN @sort_column = 'apply_date' THEN 'i.BET_APPLY_DATE'
END

SET @sql = N'
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 ' + @sort_col_real + ' ' + @order + '
OFFSET @page_size * (@page_no - 1) ROWS 
FETCH NEXT @page_size ROWS ONLY'

SET @params = N'@srch_text varchar(50), @page_no int, @page_size int'

EXEC sp_executesql @sql, @params, 
    @srch_text = @srch_text, 
    @page_no = @page_no, 
    @page_size = @page_size

内容的提问来源于stack exchange，提问作者hotflix