5

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.

11

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.

10

I find the simple syntax the more readable and less confusing. Why use functions (that can throw these weird errors) when you can check whether all variables (one by one) are NULL?:

CASE WHEN (@a IS NULL AND @b iS NULL .... AND @x iS NULL)
         THEN 'yes'
         ELSE 'no'
END

Alternative conditions - although less readable in my opinion would be:

CASE WHEN NOT EXISTS 
          ( SELECT @a, @b, @c, @d, @e
            EXCEPT
            SELECT NULL, NULL, NULL, NULL, NULL
          ) 
         THEN 'yes'
         ELSE 'no'
END

and

CASE WHEN EXISTS 
          ( SELECT @a, @b, @c, @d, @e
            INTERSECT
            SELECT NULL, NULL, NULL, NULL, NULL
          ) 
         THEN 'yes'
         ELSE 'no'
END

These last two options look similar to another (standard SQL but not yet implemented in SQL Server) option:

CASE WHEN (@a, @b, @c, @d, @e)
          IS NOT DISTINCT FROM
          (NULL, NULL, NULL, NULL, NULL) 
         THEN 'yes'
         ELSE 'no'
END
4

COALESCE must return a valid datatype.

I think your problem is related to the way it works to determine datatype to return.

A quick workaround can be:

IF (@StartDate IS NULL AND @StateCode IS NULL AND ....)
BEGIN
    SELECT 'Yes'
END
ELSE
    SELECT 'No'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.