Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
hog
Constraint Violating Yak Guru
284 Posts |
Posted - 2005-06-03 : 10:33:41
|
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)='' ASexec 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 |
|
hog
Constraint Violating Yak Guru
284 Posts |
Posted - 2005-06-03 : 11:00:34
|
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)='' ASCREATE 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 @WhereClauseselect * 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. |
|
|
hog
Constraint Violating Yak Guru
284 Posts |
Posted - 2005-06-03 : 11:09:48
|
In fact this slight modification does the job a treat!CREATE PROCEDURE dbo.ou_shrinkwrap_summary_sp @WhereClause varchar(1024)='' ASCREATE 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 @WhereClauseselect 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 :) |
|
|
|
|
|
|
|