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"James" <Jamesmitchard@.yahoo.co.uk> wrote in message
news:19d01a84.0501261535.1d7c6dd7@.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
No comments:
Post a Comment