I've been saying for years that if you are assigning an aggregate value to a variable that you need to handle if there are no rows returned. ie:
DECLARE @MyVar INT
SELECT @MyVar = SUM(t.MyValue) FROM dbo.MyTable t WHERE /* clause */
IF @MyVar IS NULL
SET @MyVar = 0
However a developer recently pointed out that they don't need to do the @@ROWCOUNT check as the SUM always returns a value (even if there are no rows). Upon doing some further digging I've found that there seems to be inconsistent behaviour from SQL Server:
So if I run:
DECLARE @MyTable TABLE(ID INT, MyValue INT)
/* you get a value of 0 back */
SELECT ISNULL(SUM(t.MyValue),0) FROM @MyTable t WHERE t.ID = 100
I get a single row with a value of 0 back.
However if I add on a GROUP BY clause:
DECLARE @MyTable TABLE(ID INT, MyValue INT)
/* when you add on a GROUP BY, you no longer get a record back */
SELECT ISNULL(SUM(t.MyValue),0) FROM @MyTable t WHERE t.ID = 100 GROUP BY t.ID
I get no rows back (which is what I expected), I've done some digging into the MS documentation but can find no reference to this difference in behaviour. Can anyone explain why this is occuring? Also is this a change in how SQL Server works?
Note: My tests have been in SQL Server 2008R2
** Edit: looked back on some emails I'd sent in the past and corrected my check, I've been saying If the value is still NULL handle it... (handles no rows as well as assigned NULL)