Select case returning an error when both elemements not varchar in some cases-CodePudding

I wanted to return a value formatted with commas at every thousand if a number or just the value if it wasn't a number I used the following statement which returned the error:

Conversion failed when converting the nvarchar value '1,000' to data type int.

Declare @QuantityToDelete int = 1000

SELECT CASE 
    WHEN ISNUMERIC(@QuantityToDelete)=1 
        THEN format(cast(@QuantityToDelete as int),'N0') 
        ELSE @QuantityToDelete 
END [Result]

I can get it to work by using the following

SELECT CASE 
    WHEN ISNUMERIC(@QuantityToDelete)=1 
        THEN format(cast(@QuantityToDelete as int),'N0') 
        ELSE cast(@QuantityToDelete as varchar) 
END [Result]

Result=1,000

Why doesn't the first example work when the ELSE @QuantityToDelete part of the statement isn't returned?

If I use the below switching the logic condition

SELECT CASE 
    WHEN ISNUMERIC(@QuantityToDelete)=0 
        THEN format(cast(@QuantityToDelete as int),'N0') 
        ELSE @QuantityToDelete 
END [Result]

Result=1000

Which is expected, but no error, the case statement still has unmatched return types an nvarchar and an int as in the first example just different logic?

CodePudding user response：

The important point to note is that a case expression returns a single scalar value, and that value has a single data type.

A case expression is fixed, it must evaluate the same and work the same for that query at runtime no matter what data flows through the query - in other words, the result of the case expression cannot be an int for some rows and a string for others.

Remember that the result of a query can be thought of, and used as, as a table - so just like a table where you you define a column as being a specific data type, you cannot have a column where the data type can be different for rows of data.

Therefore with a case expression, SQL Server must determine at compile time what the resulting data type will be, which it does (if necessary) using data type precedence. If the case expression has different data types returned in different execution paths then it will attempt to implicitly cast them to the type with the highest precedence.

Hence your case expression that attempts to return two different data types fails because it's trying to return both a nvarchar and int and SQL Server is implicitly casting the nvarchar value to an int - and failing.

The second one works because you are controlling the casting and both paths result in the same varchar data type which works fine.

Also note that when defining a varchar it's good practice to define its length also, you can easily get complacent as it works here because the default length is 30 when casting however the default is 1 otherwise.

See the relevant part of the documentation