Returning a varchar value from a coalesced int calculation

Question

I'm a newbie learning my way around T-SQL using the AdventureWorks2012 database. I'm using SQL Server 2014, though a solution that would also work with 2008 would be great. I've been given the below exercise:

Write a query using the Sales.SpecialOffer table. Display the difference between the MinQty and MaxQty columns along with the SpecialOfferID and Description columns.

Thing is, MaxQty allows for null values, so I'm trying to come up with a real world solution for an output that doesn't involve leaving nulls in there. However, when I try to use coalesce to return 'No Max' (yes, I get that I could just leave NULL in there but I'm trying to see if I can figure this out), I get the message that the varchar value 'No Max' couldn't be converted to data type int. I'm assuming this is because MaxQty - MinQty as an int takes precedence?

select 
    specialofferid
    , description
    , coalesce((maxqty - minqty),'No Max') 'Qty_Difference'
from 
    sales.specialoffer;

Error:

Msg 245, Level 16, State 1, Line 135
Conversion failed when converting the varchar value 'No max' to data type int.

I thought about just returning a nonsense integer (0 or a negative) but that doesn't seem perfect - if return 0 I'm obscuring situations where the result is actually zero, etc.

Thoughts?

Answer 1

You just need to make sure that all the parameters of the COALESCE function call have consistent data types. Because you can't get around the fact No Max is a string, then you have to make sure that the maxqty - minqty part is also treated as a string by casting the expression.

select specialofferid
, description
, coalesce(cast(maxqty - minqty as varchar),'No Max') 'Qty_Difference'
from sales.specialoffer;

EDIT: A few more details on the cause of the error

Without the explicit cast, the reason why the COALESCE function attempts to convert the No Max string to an int can be explained by the following documented rule:

Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

And if you check the precedence of the different types, as documented here, then you will see that int has higher precedence than varchar.

So as soon as you have a mix of data types in the call to COALESCE, SQL Server will try to convert all mismatching parameters to the data type with highest precedence, in this case int. To override that default behavior, explicit type casting is required.