CODEWITHSUNDEEP
sql-servercastingcase
hatchetaxesaw
hatchetaxesaw

Reputation: 183

SQL Server CASE expression not evaluating as expected

I suspect that I am not understanding how the CASE expression evaluates.

When I run this statement, I'm getting:

Msg 242, Level 16, State 3, Line 7
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

which, I understand; however, I'm not sure why. In the CASE expression, WHEN 1 should be evaluated and no CAST should take place.

Am I misunderstanding how CASE works?

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

Functionally, what I'm trying to do is look up a parameter (value is stored 'generically' as nvarchar) and then convert it to the proper datatype based on the type (as stored in parametertype). The CTE is used to look for a user-specific parameter value or fallback to the default value. I know that there are different/better ways of dealing with default parameters.

Upvotes: 1

Views: 45

Answers (1)

marc_s
marc_s

Reputation: 754438

Yes, you've misunderstood how CASE works.

First of all, it's an expression (like a+b) - not a statement.

Therefore, CASE has to return one atomic value (from several possibilities) - and all those return values have to be of the same datatype.

In your case - this is totally not the case; in such a situation, SQL Server will pick the datatype that's highest on the data type precedence list - which here is DATETIME.

So all values being returned will be attempted to be converted to DATETIME - and if P1.ParameterValue for WHEN 1 isn't a valid DATETIME, this error occurs.

Upvotes: 1

Related Questions