Geek Answers Handbook

Counting records in SQL Server

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 the desired result.

I need to ask if there is the shortest way to count the records obtained during this SP. E.g. i try so hard

 select count(*) from sp_GetRecTitleVeh

I know 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 an idea how I can count the records obtained during SP.

Thank you for sharing your valuable time.

+4
source share
3 answers

Try ...

 EXEC sp_GetRecTitleVeh SELECT @@Rowcount
+5
source

immediately after selection, select @@ rowcount. this will give you the number of rows affected.

are also starting to use the correct join syntax. The old syntax for left (=) and right jons (=) is deprecated.

+1
source

I believe that @@rowcount is the preferred approach.

But, just for the record, if you are handling the SqlCommand.StatementCompleted event, you can get a DONE_IN_PROC message that is returned to the client. This includes the number of rows affected. But you cannot use SET NOCOUNT ON if you want to get DONE_IN_PROC , so performance will be a bit difficult if you do.

+1
source

More articles:


All Articles