In SQL Server, the ISNULL function takes two arguments – the first is a potentially NULL value, and the second is another potentially NULL value. If the first value is NULL, the function returns the second value instead. It’s always been this way – for example:
SELECT V1, V2,
ISNULL(V1, V2) as [Result],
Descr
FROM (SELECT 10 as V1, 20 as V2, 'Returns first' as Descr UNION
SELECT 10 as V1, NULL as V2, 'Returns first' as Descr UNION
SELECT NULL as V1, 20 as V2, 'Returns second' as Descr UNION
SELECT NULL as V1, NULL as V2, 'Both NULL - return NULL' as Descr) t
In SQL Server, you get the following results:
V1 V2 Result Descr 10 20 10 Returns first 10 NULL 10 Returns first NULL 20 20 Returns second NULL NULL NULL Both NULL - return NULL
However, SQL Compact implements ISNULL differently, in a way that’s not compatible at all – instead, the ISNULL function returns a 0 or 1 based on whether the first parameter is NULL! It doesn’t even process the second parameter, but instead of failing with an “Invalid Syntax” error, it happily accepts it, executes, and returns unexpected results. In SQL Compact, you get the following result set:
V1 V2 Result Descr 10 20 0 Returns first 10 NULL 0 Returns first NULL 20 1 Returns second NULL NULL 1 Both NULL - return NULL
Notice the difference? The ISNULL column is returning the value based only on the first parameter (a “0” if it’s not NULL, and a “1” if it is), and if you’re not expecting it, it silently mis-calculates any formulas containing that function. If you’re relying on the function in any statements that add values together or make decisions based on the results, you’re in for some very expected behavior (in my case, I noticed totals that were wildly incorrect).
As an alternative, you should be using the COALESCE – it accepts multiple parameters and it walks down the list until it finds one that’s not null, returning a NULL value if all parameters are NULL. This function can be used in place of ISNULL in SQL Server code with exactly the same behavior (i.e. no changes), and as a bonus, it acts the same in SQL Compact, so your formulas will work properly there as well. The example above becomes:
SELECT V1, V2,
COALESCE(V1, V2) as [Result],
Descr
FROM (SELECT 10 as V1, 20 as V2, 'Returns first' as Descr UNION
SELECT 10 as V1, NULL as V2, 'Returns first' as Descr UNION
SELECT NULL as V1, 20 as V2, 'Returns second' as Descr UNION
SELECT NULL as V1, NULL as V2, 'Both NULL - return NULL' as Descr) t
And on both SQL Server and SQL Compact, the result set is the same:
V1 V2 Result Descr 10 20 10 Returns first 10 NULL 10 Returns first NULL 20 20 Returns second NULL NULL NULL Both NULL - return NULL
+1 for consistency!
MORAL of the story:
When executing statements on different platforms, ensure the functions you use behave in the same manner on both – and even when considering multiple platforms from the same vendor, named almost identically, don’t assume things behave unless you’ve tested them 🙂