use of coalesce to check for all variables being null

How can I use coalesce to check for all variables with different type precedence to all be NULL without encountering errors.

DECLARE 
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@StateCode CHAR(2) = NULL,
@CountyCode CHAR(3) = NULL,
@ProducerName VARCHAR(64) = null,
@TaxID VARCHAR(9) = null,
@Farm INT = null

SELECT CASE WHEN COALESCE(
    @StartDate, 
    @EndDate, 
    @StateCode, 
    @CountyCode, 
    @ProducerName, 
    @TaxID, 
    @Farm) IS NULL 
THEN 'yes' ELSE 'no' END

Output: 'yes'

adding SELECT @StateCode = '22' gives this error

Msg 241, Level 16, State 1, Line 94
Conversion failed when converting date and/or time from character string.

this is valid

SELECT CASE WHEN 
   COALESCE(@StartDate, @EndDate) IS NULL 
   AND COALESCE(@StateCode, @CountyCode, @ProducerName, @TaxID) IS NULL 
   AND @farm IS NULL 
THEN 'yes' ELSE 'no' END

I'd love to know if there's another clever way to accomplish this in a terse manner so I don't have to remember to separate by type family.

As long as all the variables involved are of datatypes compatible with sql_variant (they are in this case - basically no LOB datatypes, CLR types, or user defined datatypes) then you can use

SELECT CASE WHEN COALESCE(
    CAST(@StartDate as sql_variant), 
    @EndDate, 
    @StateCode, 
    @CountyCode, 
    @ProducerName, 
    @TaxID, 
    @Farm) IS NULL 
THEN 'yes' ELSE 'no' END

It is only necessary to cast one of the arguments as COALESCE


Returns the data type of expression with the highest data type
precedence.

And sql_variant has a very high datatype precedence (only beaten by user-defined data types and they would prevent the method from working anyway).

Personally I don't find this any more understandable then just finding the conjunction of the individual IS NULL results though.

StackExchange Database Administrators Q#140909, answer score: 11