To find out row count from execution results of SP

Hi experts,
I need to find out the row count from the result of execution of the SP, for ex: if we do
exec SP_MAIN, we get 2000 records, I need to get the answer as just 2000 and not the results, please let me know if this is possible
Who is Participating?

Improve company productivity with a Business Account.Sign Up

SharathConnect With a Mentor Data EngineerCommented:
try creating a temp table or table variable and insert the result of SP into that table. After that, you can query the count of records in that table.
declare @table table (col_1 int, col_2 int) -- add as many columns with correct datatype which is identical to the result set of SP_MAIN
insert @table
exec SP_MAIN -- insert the result of SP to the table variable
select COUNT(*) from @table -- Get the count of records from the table

Open in new window

Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
Unless I am over simplifying the following works

Within SP_MAIN add a select statement to set the value of a variable example @count int to the number of records matching the query criteria.

Return the value of @count as the resultset
shyamaladevibAuthor Commented:
Thanks, that would have been an option but I am not allowed to change the procedure, please suggest alternative way
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
Are you dealing strictly within SQL? or are you using ADO to retreive records into a resultset within a different programming language?  

If the latter, you might be able to use the functionality of ADO or Other to get the rowcount when processing the resultset.
MSSystemsConnect With a Mentor Commented:
Sharath_123 has a good idea although it is going to be costly. Doubeling the execution time. Try something like this.


exec SP_MAIN

Return @@RowCount


Open in new window

Note: You can change the return to a select if you are not making use of the output value variable.
SharathData EngineerCommented:
MSSystems -  thats a good suggestion. As the asker mentioned that he/she does not want the result set, I am inserting the result set into a temp table.
Thank you for pointing that out.

Let me clarify. From SSMS you will see both, but in my opinion from a performance point of view. I believe this option to be the lesser evil. Considering that you are not adding unwanted duplicated data to the tempDB.

The other consideration is when you call this from another application (VB, ASP.NET, SSIS or even SSRS). Then you can choose to view the output only. This will give the desired result.

The only correct solution here would be to change the stored procedure to return the desired result. Unfortunately this is not allowed in this scenario.
shyamaladevibAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.