Can you just run mutiple BEGIN - END in you SPROC to do different stuff.. like If you want to do this in the same procedure.. you would have to declare local vars Declare @DetailCount Set @DetailCount = Select count(whatever) from DetailTable where whatever and do if statement later...
OR u can use some like the below //returns Count of detail lines if @ProcLevel = 1 BEGIN select A.Header,B.Count(Myfield) as 'detailCount' from Header A LEFT JOIN DETAIL B ON A.HeaderID=B.HeaderID where ....whatever clause END ELSE select A.Header,B.detailLine as 'detailCount' from Header A LEFT JOIN DETAIL B ON A.HeaderID=B.HeaderID where ....whatever clause END Joe Certified Advanced ColdFusion Developer ----- Original Message ----- From: "Houk, Gary" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, July 24, 2002 10:23 AM Subject: RE: OT - Stored Procedure Help > Hello, > > Here is some more detail: > > 2 tables - weborderheader,weborderdetail > 1 stored procedure joining the 2 tables on the batch_number field. 1 input for the procedure - @searchstring > > Objective: pass either a btn or batch_number to the stored procedure. The stored procedure should then > return the results of the search to a page where I will display the name, btn, and orderdate.The orders > can match either the btn (could be multiple orders) or batch_number (will ALWAYS be one order). They will > click a detail link where I will run the sproc again, this time showing all of the fields available, with the > multiple detail lines. > > The sproc works fine as long as there is only one detail line. If there is more than one detail line, then I will > get a recordcount back that is equal to the number of detail items. > > Thanks, > > - Gary > > -----Original Message----- > From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, July 24, 2002 10:10 AM > To: CF-Talk > Subject: Re: OT - Stored Procedure Help > > > > I have a header and detail table. The detail tables stores product(s) > > associated with the header (user information). Whenever I have more than > > one detail line, I get more than one record returned. I only need to get > > the amount of headers back that match the input, not detail. > > I'm not sure I understand the question. Maybe it would be helpful to know > what this query will be used for. The query you have here returns detail > information like the product_id, quantity and item_price ... are you using > this information after you return it to the cf page and if so, how so? > > There are several ways to limit the result set to only the headers that > match your search info, but they will produce different results, i.e. You > could use a group by clause and get aggregated information from the detail > table, in other words, total number of all products ordered, total price of > all products ordered. You could remove the detail table from the query > all-together if you don't need the detail data at all. Or if you need the > individual detail data but were hoping to get it in separate columns because > you weren't sure how to display them properly on the page, you would > probably want to use an order by clause and group the output in ColdFusion > > <cfoutput query="mystoredprocedure" group="btn"> > ... header info here ... > <cfoutput>... detail info here... </cfoutput> > </cfoutput> > > hth > > > > CREATE PROCEDURE spWEBOrderHeaderDetailLookup > > @SearchString char(10) > > AS > > > > SELECT oh.rssc_rep_id, > > oh.office_code, > > oh.entry_method, > > oh.new_connect_flag, > > oh.shipping_method, > > oh.payment_method, > > oh.delivery_date, > > oh.btn, > > oh. btn_cust_code, > fyi ^ extra space in your column definition > > I almost didn't notice it, figured it was a typo. :) > > > Isaac Dealey > > www.turnkey.to > 954-776-0046 > > ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
