@@Rowcount

B

barski

Well-known member
Joined
Apr 7, 2002
Messages
239
Location
Tennessee
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?
 
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(...) youre 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:
Code: 
DECLARE @count int
SELECT @count = SUM(...) FROM table1 WHERE...

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

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

Code: 
        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
 
You probably should wrap the query with IsNull or COALESCE to convert the sum. Something like:
Code: 
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
 
Back
Top