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.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Stored procedure and its uses

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)='' 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 - 2005-06-03 : 10:48:57
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
Go to Top of Page

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)='' 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

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)='' 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
   

- Advertisement -