sql-server - SQL Server CASE 表达式未按预期计算

问题描述

我怀疑我不理解 CASE 表达式的计算方式。

当我运行此语句时，我得到：

消息 242，级别 16，状态 3，第 7
行将 nvarchar 数据类型转换为 datetime 数据类型导致值超出范围。

我明白了；但是，我不确定为什么。在CASE表达式中，WHEN 1应该评估并且不CAST应该发生。

我误解了它的CASE工作原理吗？

DECLARE @parameterID int = 42;
DECLARE @userID int = 3;
DECLARE @administratorID int = 2;

--parameterid of 42 has a parametertype of 1

With Param AS  
(
    SELECT 
        ParameterValue, USER_ID, P.PARAMETERTYPE_ID AS ParameterType 
    FROM 
        dbo.ParameterValues PV 
    INNER JOIN 
        Parameters P on P.ID = PV.PARAMETER_ID
    WHERE 
        PARAMETER_ID = @parameterID AND USER_ID = @userID

    UNION ALL

    SELECT 
        ParameterValue, USER_ID, P.PARAMETERTYPE_ID AS ParameterType 
    FROM 
        dbo.ParameterValues PV 
    INNER JOIN 
        Parameters P on P.ID = PV.PARAMETER_ID
    WHERE 
        PARAMETER_ID = @parameterID AND USER_ID = @administratorID
)
SELECT TOP 1 
    TypedParmValue = 
    CASE P1.ParameterType
        WHEN 1 THEN P1.ParameterValue
        WHEN 2 THEN CAST(P1.ParameterValue AS datetime) 
        WHEN 3 THEN CAST(P1.ParameterValue AS DECIMAL(20,6))
        WHEN 4 THEN CAST(P1.ParameterValue as bit)
        ELSE P1.ParameterValue
    END,
    P1.ParameterType,
    P1.ParameterValue,
    PT.ParameterTypeName 
FROM 
    Param P1
INNER JOIN 
    ParameterTypes PT on P1.ParameterType = PT.ID
ORDER BY
    USER_ID DESC

从功能上讲，我要做的是查找一个参数（值“一般”存储为 nvarchar），然后根据类型（存储在参数类型中）将其转换为正确的数据类型。CTE 用于查找用户特定的参数值或回退到默认值。我知道处理默认参数有不同/更好的方法。

标签： sql-servercastingcase

是的，你误解了它的CASE工作原理。

首先，它是一个表达式（如a+b）——而不是一个语句。

因此，CASE必须返回一个原子值（来自多种可能性）——并且所有这些返回值都必须是相同的数据类型。

在您的情况下-完全不是这种情况；在这种情况下，SQL Server 将选择数据类型优先级列表中最高的数据类型——这里是DATETIME.

因此，所有返回的值都将尝试转换为DATETIME- 如果P1.ParameterValueforWHEN 1不是有效的DATETIME，则会发生此错误。

sql-server - SQL Server CASE 表达式未按预期计算

问题描述

解决方案

推荐阅读