To find out row count from execution results of SP

Posted on 2010-11-10
Last Modified: 2012-05-10
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
Question by:shyamaladevib
  • 3
  • 2
  • 2
  • +1
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 34105547
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

Author Comment

ID: 34105577
Thanks, that would have been an option but I am not allowed to change the procedure, please suggest alternative way
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 34105901
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.
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

LVL 40

Accepted Solution

Sharath earned 250 total points
ID: 34108002
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


Assisted Solution

MSSystems earned 250 total points
ID: 34115206
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


Expert Comment

ID: 34115341
Note: You can change the return to a select if you are not making use of the output value variable.
LVL 40

Expert Comment

ID: 34115407
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.

Expert Comment

ID: 34116067
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.

Author Closing Comment

ID: 34175120

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Managing Columnstore Indexes 2 27
Generate Weekly Schedule 15 28
Trying to identify overlapping date ranges 5 20
Addition to SQL for dynamic fields 6 36
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now