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
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.