SP to summarize data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JimP - 25 Jul 2008 14:12 GMT

I have an sp that selects data at a detail level. Can I create an sp to
summarize the data in the detail sp (by calling it), or do I need to create
a single sp that selects and summarizes the data?

Essentially, can one sp call another sp?

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 25 Jul 2008 14:17 GMT

Yes, a procedure can call a procedure, however it is not as easy as SELECT
SUM(col) FROM (EXEC other_sp) ... assuming you want to avoid performing the
same calculations / joins in multiple places, it is possible that you can
change the query in the original SP to a view, putting all of the
calculations / joins there, and both SPs can reference the view in slightly
different ways. But I don't know enough about your procedure to know if
that is a good way to do it or not. For other approaches see:

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

On 7/25/08 9:12 AM, in article dsadnQoaqJWvTBTVnZ2dnUVZ_qLinZ2d@comcast.com,

> I have an sp that selects data at a detail level. Can I create an sp to
> summarize the data in the detail sp (by calling it), or do I need to create
> a single sp that selects and summarizes the data?
>
> Essentially, can one sp call another sp?

Reply to this Message

vinu - 25 Jul 2008 14:38 GMT

to add to Aaron post, up to 32 levels nested sp call is suported in sql
server. In your case I don't think you need to write another SP to
summarise, you can do it in one single sp Following links might be usefull
to you

http://msdn.microsoft.com/en-us/library/aa214411(SQL.80).aspx
http://www.4guysfromrolla.com/webtech/111499-1.shtml
http://www.sqlservercentral.com/articles/Basic+Querying/nestingstoredprocedures/998/

Signature

vinu
http://oneplace4sql.blogspot.com/

> Yes, a procedure can call a procedure, however it is not as easy as SELECT
> SUM(col) FROM (EXEC other_sp) ... assuming you want to avoid performing
[quoted text clipped - 17 lines]
>>
>> Essentially, can one sp call another sp?

Reply to this Message

--CELKO-- - 25 Jul 2008 21:17 GMT

>> Essentially, can one stored procedure call another stored procedure? <<

The short answer is "yes", but the better answer is to use a VIEW that
does the summary. Every time the VIEW is invoked, you know you will
get the right answer. VIEWs are portable and stored procedures are
not.

Reply to this Message