sql - 在动态 ORDER BY 中将 nvarchar 转换为 int 时出错

Question

SQL 代码:

DECLARE @SortOrder nvarchar(max); 
SET @SortOrder = 'name';
SELECT * FROM [database_name].[schema_name].[table_name] 
ORDER BY CASE @SortOrder
    WHEN 'id' THEN id 
    WHEN 'name' THEN name 
    END;

输出:消息 245，级别 16，状态 1，第 3 行将 nvarchar 值 'foo' 转换为数据类型 int 时转换失败。

当我改用 @SortOrder = 'id' 时，它可以完美运行。当我执行正常的 SELECT 和 ORDER BY name 时，它​​也能正常工作。

为什么它会尝试将该 nvarchar 值转换为数据类型 int，我该如何阻止它这样做？

Answer 1

CASE 是一个返回特定数据类型的单个值的表达式。潜在值必须兼容，并且由于您的名称值必须为 foo，它无法转换为 INT，因此您会收到此错误。解决此问题的最简单方法是按数据类型分隔 ORDER BY 表达式:

ORDER BY CASE @SortOrder WHEN 'id' THEN id END,
         CASE @SortOrder WHEN 'name' THEN name END;

另一种选择(只要您启用了optimize for ad hoc workloads，它可能更适合计划缓存)是动态 SQL:

DECLARE @sql NVARCHAR(MAX) = N'SELECT ... ORDER BY ' + QUOTENAME(@SortOrder) + ';';

EXEC sp_executesql @sql;

也不确定为什么要给 @SortOrder 最大数据类型 - 任何列名都不可能接近那么大。