Solved

To find out row count from execution results of SP

Posted on 2010-11-10
9
412 Views
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
0
Comment
Question by:shyamaladevib
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
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
0
 

Author Comment

by:shyamaladevib
ID: 34105577
Thanks, that would have been an option but I am not allowed to change the procedure, please suggest alternative way
0
 
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.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 41

Accepted Solution

by:
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

0
 
LVL 4

Assisted Solution

by:MSSystems
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.

CREATE PROCEDURE GetSPCount 
AS
BEGIN

exec SP_MAIN

Return @@RowCount

END
GO

Open in new window

0
 
LVL 4

Expert Comment

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

Expert Comment

by:Sharath
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.
0
 
LVL 4

Expert Comment

by:MSSystems
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.
0
 

Author Closing Comment

by:shyamaladevib
ID: 34175120
thanks!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

615 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