Getting no. of rows affected after running select query in SQL Server 2005

Question

Hi friends,

Below is my query

select    
@monNameStr as [MName],       
IsNull(count(c.AssignmentID),0),          
IsNull(sum(s.ACV),0),         
IsNull(sum(s.GrossReturn),0),         
IsNull(sum(s.NetReturn),0),       
IsNull(avg(a.Total),0)       
FROM

dbo.Assignment_ClaimInfo c,       
dbo.Assignment_SettlementInfo s,          
dbo.Assignment_AdvCharges a      

Where
c.Assignmentid=s.Assignmentid and         
s.Assignmentid=a.Assignmentid and         
a.Assignmentid in          

(select AssignmentID from dbo.Assignment_ClaimInfo         
where (upper(InsuranceComp)=upper(@CompName) or upper(@CompName)='ALL COMPANIES') 
and (DateName(month,DATEADD(month, 0, DOFileClosed))+' '
+cast(year(DATEADD(month, 0, DOFileClosed)) as varchar)=@monNameStr))
Group By c.InsuranceComp
Order By c.InsuranceComp

where @monNameStr is calculated date field like 'October 2009'

What i need to know the no. of records affected by this select query.

I DONT NEED TO NEST THIS QUERY TO ANOTHER QUERY WITH COUNT() FUNCTION.

Your valuable help is appreciated.

Answer 1

You can check the value of @@ROWCOUNT after the query has run. See http://technet.microsoft.com/en-us/library/ms187316.aspx for more info.

Answer 2

select @@ROWCOUNT

(e.g. http://stackoverflow.com/questions/174143/counting-the-number-of-deleted-rows-in-a-sql-server-stored-procedure )

Answer 3

capture @@ROWCOUNT into a variable, because it will change values each time you select it:

DECLARE @Rows   int

---your query here

SELECT @Rows=@@ROWCOUNT

you can then use it as necessary as @Rows

Answer 4

You can just use `@@ROWCOUNT` to get the records affected/returned

DECLARE @rowsreturned INT
SET @rowsreturned = @@ROWCOUNT