Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored Procedure Record Count

Posted on 2009-12-30
12
Medium Priority
?
561 Views
Last Modified: 2012-06-27
Hello,

I have a series of stored procedures already created.  I need a new stored procedure which accepts the name of a current stored procedure (e.g. @spName) and then returns its record count.

So, for example, if I have the following procs:
usp_GetSomeData
usp_GetSomeMoreData
usp_GetEvenMoreData

I'm looking for "usp_GetRowCount" which will return the number of records from any of the above procedures.
0
Comment
Question by:Torrwin
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26148649
its always better to write another procedure for this, otherwise you need to populate the results of the stored procedures into a temp table and take the count from there, thats really not a good idea..
0
 
LVL 13

Author Comment

by:Torrwin
ID: 26148676
>>its always better to write another procedure for this
That's what i'm trying to do...

Is it not possible to execute one stored procedure from another?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26148989
its possible

create procedure testsp
@spname varchar(100)
as
if @spname = 'usp_GetSomeData'
  exec usp_GetSomeData
else if @Spname = 'usp_GetSomeMoreData'
  exec usp_GetSomeMoreData
else
 exec usp_GetEvenMoreData
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Author Comment

by:Torrwin
ID: 26149003
I've figured it out.
CREATE PROCEDURE usp_CountStoredProc(@procName VarChar(50))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @CMD1 VARCHAR(8000)
	
	SELECT @CMD1 = '[dbo].[' + @procName + ']'
	EXEC @CMD1
	
	SELECT @@Rowcount
END
GO

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26149076
that will retrun you both the recordset and the rowcount .. are you sure you need both of these ?
0
 
LVL 13

Author Comment

by:Torrwin
ID: 26149601
Hmm, you're right.  Any suggestions?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26150938
create another sp, which just returns the count
for example  say the sp  usp_GetSomeData  has the following statement
 select col1 , col2  from table1 where col4 = 'abc'
create another sp with the following statement just for the count
select count(*) from  from table1 where col4 = 'abc'
 
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26151268
Use @@ROWCOUNT to return the number of rows affected or use your front-end app to report the same, without the need for additional Stored Procedures.
0
 

Expert Comment

by:ibrahimammari
ID: 26152823
great solution
0
 
LVL 13

Author Comment

by:Torrwin
ID: 26153631
aneeshattingal,
I understand you're trying to steer me towards creating a new stored procedure for each one I already have.  However, rather than creating 20 new stored procedures, I would rather create just one that is more efficient.  If you don't think it is possible, please just say so. =P

acperkins,
Thanks, that's what I'm currently doing.  However, i'm trying to make said application more efficient by moving the processing onto the server and off of the client.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26155034
>> However, i'm trying to make said application more efficient by moving the processing onto the server and off of the client.<<
Neither one of the solutions I provided are processed by the client, they are both done on the server and returned by it as properties of the method executed by the front-end app.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 26155045
>> If you don't think it is possible, please just say so. <<
Probably not without resorting to using Dynamic SQL and all the problems that entails.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

810 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