Sql Aggregate Results of a Stored Procedure

Question

I currently have a stored procedure that returns a list of account numbers and associated details. The result set may contain multiple entries for the same account number. I also want to get some aggregate information such as how many distinct accounts are contained within a particular result set. Is there some way to retrieve such a view from my stored procedure results such as

SELECT AccountNumber, Count(*) 
FROM mystoredproc_sp 
GROUP BY AccountNumber

It's fine if it needs to be contained within another stored procedure, but I'd like to be able to at least benefit from the logic that already exists in the first SP without duplicating the bulk of its code.

Answer 1

You would have to move your query into a table-valued function and call it from both stored procedures - the old one and the new one. That way you have the query in only one place. It is not possible to select from a stored procedure result set.

Answer 2

DECLARE @tt TABLE (acc INTEGER)
INSERT INTO @tt EXECUTE mystoredproc_sp
SELECT acc, COUNT(*) FROM @tt GROUP BY acc