By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

Possible?: Count(*) returned by EXEC

P: n/a
Hi all,

I have a stored procdure which does a select and returns the records
directly -i.e. Not in output parameters e.g:

CREATE PROCEDURE up_SelectRecs(@ProductName nvarchar(30)) AS

SELECT *
FROM MyTable
WHERE [Name]=@ProductName

In another stored procedure I need to do the following:

SELECT COUNT(*)
FROM MyTable
WHERE [Name]=@ProductName

As the select queries are actually a lot more complex that this, I'd
rather not duplicate the select code in 2 sp's to save the maintenance
effort - I'm looking for a way to execute the first procedure from the
second and just count the records returned - something like:

SELECT Count(*)
FROM EXEC up_SelectRecs @ProductName

Any way to achieve this?

Thanks all

--James
Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"James" <Ja***********@yahoo.co.uk> wrote in message
news:19**************************@posting.google.c om...
Hi all,

I have a stored procdure which does a select and returns the records
directly -i.e. Not in output parameters e.g:

CREATE PROCEDURE up_SelectRecs(@ProductName nvarchar(30)) AS

SELECT *
FROM MyTable
WHERE [Name]=@ProductName

In another stored procedure I need to do the following:

SELECT COUNT(*)
FROM MyTable
WHERE [Name]=@ProductName

As the select queries are actually a lot more complex that this, I'd
rather not duplicate the select code in 2 sp's to save the maintenance
effort - I'm looking for a way to execute the first procedure from the
second and just count the records returned - something like:

SELECT Count(*)
FROM EXEC up_SelectRecs @ProductName

Any way to achieve this?

Thanks all

--James


See here:

http://www.sommarskog.se/share_data.html

If you have SQL 2000 (you didn't mention which version you have), a
table-valued UDF would probably work well in your case:

select * from dbo.MyFunc(@ProductName)
select count(*) from dbo.MyFunc(@ProductName)

Simon
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.