I have an empty table SP.NotificationRuns. I have a stored procedure which includes the following:

	select
		@from = max(WhenAttempted)
	from
		SP.NotificationRuns
	where
		WhenAttempted < @today

	if @@ROWCOUNT = 0
	begin
		set @from = datetimefromparts(2000, 1, 1, 0, 0, 0, 0)
	end

When I step through this with the debugger, after performing the select statement, @@ROWCOUNT contains 1 and so it fails to set up the lower bound in the @from variable.

Can anyone suggest why please?

Michael

Hi Michael, You are returning NULL hence you have RowCount = 1, See two examples below, both query checks whether a record exists or not.

-- Row Count will be greater than zero as record exists
CREATE TABLE #NotificationRuns(WhenAttempted int)
-- Inserting one record
INSERT INTO #NotificationRuns VALUES(10);

DECLARE @from int,@rowcount int

IF EXISTS (SELECT WhenAttempted FROM #NotificationRuns)
BEGIN
	SELECT 
		@from = MAX(WhenAttempted)
	FROM #NotificationRuns
END
SET @rowcount =  @@ROWCOUNT

PRINT '@Rowcount : ' + CAST(@rowcount AS VARCHAR(10))

DROP TABLE #NotificationRuns
GO

-- Row Count = 0 as no record exists
CREATE TABLE #NotificationRuns(WhenAttempted int)


DECLARE @from int,@rowcount int

IF EXISTS (SELECT WhenAttempted FROM #NotificationRuns)
BEGIN
	SELECT 
		@from = MAX(WhenAttempted)
	FROM #NotificationRuns
END
SET @rowcount =  @@ROWCOUNT

PRINT '@Rowcount : ' + CAST(@rowcount AS VARCHAR(10))

DROP TABLE #NotificationRuns

Use COALESCE() to catch the NULL case:

DECLARE @From DATETIME;

SELECT  @From = COALESCE(MAX(T.create_date), '20000101')
FROM    sys.tables T
WHERE   T.name = 'WhatThatTableDoesNotExist?';

SELECT  @From;

• Edited by Stefan HoffmannMVP 14 hours 56 minutes ago

You can simplify your code:

select
		@from = ISNULL(max(WhenAttempted),datetimefromparts(2000, 1, 1, 0, 0, 0, 0))
	from
		 NotificationRuns
	where
		WhenAttempted < @today

The explanation is because in this case (there is no HAVING clause constraining the groups) the scalar aggregate needs to return something even if it is the NULL mark, so you always get a row.

You already got how to re-write the statement to avoid inquiring for @@ROWCOUNT.

To exmand on the other replies, the reason why you get 1:

When you use an aggregate function without GROUP BY, you *know that you always* get 1 row back. If your WHERE clause cause the aggregate function to operator over 0 rows, then it will return NULL or perhaps 0 (depends on the aggregate function, COUNT returns 0, the others returns NULL). And this is why you get 1 for @@ROWCOUNT.

I would like to enhance TiborK answer here; your actual answer is inside query plan

This is because your last operation that is calculating MAX is happened on single row (i.e. NULL row)

Best way to resolve this issue:

1.  Answer from Stefan Hoffmann or Jingyang Li
2. Use If EXISTS 
3. Use (@from IS NULL) 

Hope this will help

Like others have mentioned, a scalar aggregate always returns one row, be it with a NULL for an empty set or the actual maximum for a nonempty set. To identify an empty set, besides the classic options such as using ISNULL or EXISTS, there's another curious option that is often overlooked. Instead of using a scalar aggregate, use a grouped aggregate with an empty grouping set, as in, GROUP BY (). This way, when the input is an empty set you will get an empty set back. Here's a repro:

-- DDL and sample data
create table #empty(col1 int);
create table #onerow(col1 int);
insert into #onerow(col1) values(10);

-- both return 1
select max(col1) as maxcol1 from #onerow;
select @@rowcount as rowcountonerow;

select max(col1) as maxcol1 from #empty;
select @@rowcount as rowcountempty;

-- empty returns 0
select max(col1) as maxcol1 from #onerow group by ();
select @@rowcount as rowcountonerow;

select max(col1) as maxcol1 from #empty group by ();
select @@rowcount as rowcountempty;

Cheers,

Itzik