Counting records in SQL Server

Question

Hello friends,

Take a look at this SP.

ALTER PROCEDURE [dbo].[sp_GetRecTitleVeh] 

AS
BEGIN
select 
    a.StockNo, c.ClaimNo,
    v.VIN, v.[Year],v.Make, v.Model,
    c.DOAssign, t.DOLoss, t.RecTitleDate
From 
    dbo.Assignments a,
    dbo.Assignment_ClaimInfo c,
    dbo.Assignment_TitleInfo t,
    dbo.Assignment_VehicleInfo v
Where 
    a.AssignmentID= c.AssignmentID and
    c.AssignmentID= t.AssignmentID and
    t.AssignmentID= v.AssignmentID and
    t.RecTitleDate is not null and
    c.InsuranceComp = 'XYZ' and
    a.StockNo not in (select StockNo from dbo.Invoice where InvoiceType = 'Payment Invoice') 
order by t.RecTitleDate
END

This SP works fine and gives me required result.

What i need is to ask that is there any shortest way to count records obtained by executing this SP. For ex. i am trying like this

select count(*) from sp_GetRecTitleVeh

I know that there is a solution like -

ALTER PROCEDURE [dbo].[sp_CountRecTitleVeh] 

AS
BEGIN
select 
    count(a.StockNo)
From 
    dbo.Assignments a,
    dbo.Assignment_ClaimInfo c,
    dbo.Assignment_TitleInfo t,
    dbo.Assignment_VehicleInfo v
Where 
    a.AssignmentID= c.AssignmentID and
    c.AssignmentID= t.AssignmentID and
    t.AssignmentID= v.AssignmentID and
    t.RecTitleDate is not null and
    c.InsuranceComp = 'XYZ' and
    a.StockNo not in (select StockNo from dbo.Invoice where InvoiceType = 'Payment Invoice') 
order by t.RecTitleDate
END

Do you have any idea how could i count records got by executing SP.

Thanks for sharing your valuable time.

Answer 1

Try...

EXEC sp_GetRecTitleVeh 
SELECT @@Rowcount 

Answer 2

immediately after your select do select @@rowcount. this will give you the number of affected rows.

also start using the proper join syntax. the old syntax for left (=) and right jons (=) is deprecated.

Answer 3

I believe that @@rowcount is the preferred approach.

But, just for the record, if you handle the SqlCommand.StatementCompleted event, you can get the DONE_IN_PROC message which is returned to the client. That includes the number of rows affected. But you can't use SET NOCOUNT ON if you want to get DONE_IN_PROC, so performance will be hindered a bit if you do this.