@@RowCount return 1 even the previous select condition was False

user_4915813 0
Viewed 49

I have this simple code

declare @a int, @b int 
select @a=sum(1), @b=1 from syscolumns where 1 = 2
select @@rowcount, @a, @b

The result is : @@rowcount=1, @a=null, @b=1

@a = null is fine to me, but how come @@rowcount and @b=1

SQLFiddle : https://rextester.com/PKCB94229

sql-server-2008 sql
1 Answers

This is because scalar aggregates (no GROUP BY) always return one row even on an empty input (assuming no HAVING clause eliminates it)

select sum(1) from syscolumns where 1 = 2 returns a resultset with one row, so @@ROWCOUNT is correctly reported as 1.

If you want to count the input rows from a query that assigns scalar aggregates to variables then assigning COUNT(*) to another variable would be simplest.

user_732260
Related