CODEWITHSUNDEEP
sqlsql-server-2008
EagerToLearn
EagerToLearn

Reputation: 675

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

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

Upvotes: 1

Views: 130

Answers (1)

Martin Smith
Martin Smith

Reputation: 453028

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.

Upvotes: 2

Related Questions