@@Rowcount

[barski]

If I sum a field then the @@rowcount will always equal at least 1 or at least it appears that way. So if i only want to execute certain statements if the rowcount is "truly" equal to at least 1 then what do I do?

[Nerseus]

If you mean the SUM returns more than one, then you need to set a variable and use that. If the SUM returns any number, then @@rowcount will be 1. It might be more than one, if you have a GROUP BY. Remember that @@rowcount is the number of rows returned - in a typical SELECT SUM(...) you're returning one row, one column (the sum) - so @@rowcount will be 1. If the WHERE clause returned no records and the SELECT SUM(...) return NULL, then @@rowcount would be 0.

 

What you probably want is:

DECLARE @count int
SELECT @count = SUM(...) FROM table1 WHERE...

IF @count > 0
BEGIN
   -- Do something here
END

 

-nerseus

[barski]

it is returning a null but the @@rowcount returns 1.

 

       use northwind

      declare @thisrows int

      select sum(unitprice) from [order details] where orderid=999999

      set @thisrows=@@rowcount

      print cast(@thisrows as nvarchar)

 

I handled it with something similar to what you suggested but is not null instead of zero because this is for a billing project and even if it zero the project will still need to issue an invoice

[Nerseus]

You probably should wrap the query with IsNull or COALESCE to convert the sum. Something like:

select IsNull(sum(unitprice), 0) from [order details] where orderid=999999

 

You usually only use @@rowcount when performing an UPDATE, INSERT or DELETE to check how many rows were affected. On a SELECT, the @@rowcount is useful, but not so much when doing a SUM or other aggregate function.

 

-ner