SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Stored procedure and its uses
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

hog
Constraint Violating Yak Guru

United Kingdom
284 Posts

Posted - 06/03/2005 :  10:33:41  Show Profile
I now have a stored proc that returns all the information I require to the calling application.

The stored proc returns the data via a simple select staement after all the processing has been done.

I now want to create a similar report but one that summarises the output of the first proc.

I first thought about duplicating the first one and modifying it to output the summary info, but realise this is a crap way to do things.

So here is the setup:

The calling application passes the where clause to my first proc into a variable called @WhereClause. This proc does the business and I am happy and the external app gets the data back.

Now if I were to create the same interface on the external app which will pass exactly the same where clause to my second proc I could do something like this...

CREATE PROCEDURE SecondProc_sp @WhereClause varchar(1024)='' AS

exec FirstProc_sp @WhereClause

...summary processing code here...

...return summary data to calling app.

That makes sense in my head, but how to I get hold of the data returned from FirstProc for SecondProc to use it?

X002548
Not Just a Number

15586 Posts

Posted - 06/03/2005 :  10:48:57  Show Profile
What's the result set look like?

Alos dynamic sql again huh....how many bullets are in the chamber?

Look up Temporary Tables



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Edited by - X002548 on 06/03/2005 10:49:13
Go to Top of Page

hog
Constraint Violating Yak Guru

United Kingdom
284 Posts

Posted - 06/03/2005 :  11:00:34  Show Profile
Can you explain what you mean please? If I am doing something wrong I'd rather know a better way..

This is what I have at the moment:

CREATE PROCEDURE SecondProc_sp @WhereClause varchar(1024)='' AS

CREATE TABLE #ShrinkWrapTotals (asm_no varchar(30), part_count int, produced decimal, inserted decimal(20,8), insert_cost decimal(20,8), wrap_cost decimal(20,8), start_date datetime, end_date datetime)

INSERT #ShrinkWrapTotals (asm_no, part_count, produced, inserted, insert_cost, wrap_cost, start_date, end_date)
exec FirstProc_sp @WhereClause

select * from #ShrinkWrapTotals

--exec SecondProc_sp ' Where asm_no like ''%e123xpst1-8%'' AND start_date BETWEEN ''20050601'' AND ''20050601 23:59:59 '' AND end_date BETWEEN ''20050602'' AND ''20050602 23:59:59 '''

So now I actually do have what I want. I have the data returned from FirstProc stored in the table within SecondProc. I can now summarise that data and produce a second report with minimal code.

The user gets two reports, the first gives a breakdown of all the data in question and the second report gives the summary totals.

This to me seems a neat way to do it as I am using a stored proc rather than repeating code.

If this is crap then please enlighten me.....please.
Go to Top of Page

hog
Constraint Violating Yak Guru

United Kingdom
284 Posts

Posted - 06/03/2005 :  11:09:48  Show Profile
In fact this slight modification does the job a treat!

CREATE PROCEDURE dbo.ou_shrinkwrap_summary_sp @WhereClause varchar(1024)='' AS

CREATE TABLE #ShrinkWrapTotals (asm_no varchar(30), part_count int, produced decimal, inserted decimal(20,8), insert_cost decimal(20,8), wrap_cost decimal(20,8), start_date datetime, end_date datetime)

INSERT #ShrinkWrapTotals (asm_no, part_count, produced, inserted, insert_cost, wrap_cost, start_date, end_date)
exec ou_shrinkwrap_sp @WhereClause

select sum(insert_cost) as [Total Insert Cost], sum(wrap_cost) as [Total Wrap Cost] from #ShrinkWrapTotals

--exec ou_shrinkwrap_summary_sp ' Where asm_no like ''%e123xpst1-8%'' AND start_date BETWEEN ''20050601'' AND ''20050601 23:59:59 '' AND end_date BETWEEN ''20050602'' AND ''20050602 23:59:59 '''

--exec ou_shrinkwrap_summary_sp ' Where start_date BETWEEN ''20050601'' AND ''20050601 23:59:59 '' AND end_date BETWEEN ''20050602'' AND ''20050602 23:59:59 '''

When learning to program over the years I read many times this statement...

"The is no wrong way to program, if it works it is right. There is however inefficient and efficient ways to program!"

So you see my way does work, but if you gurus say this is the wrong way to do it please teach me so I can get better :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000