> 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.
And you want the same procedure to perform both the detail view and the list
view?
CREATE PROCEDURE spWEBOrderHeaderDetailLookup
@SearchString char(10) = NULL,
@batch_number INT = NULL
AS
IF (@batch_number IS NOT NULL) BEGIN
SELECT (all your fields) FROM weborderheader oh
INNER JOIN weborderdetail od ON ( od.batch_number = oh.batch_number )
WHERE oh.btn = @btn;
END ELSE BEGIN
SELECT batch_number, (whatever) FROM weborderheader
WHERE CONVERT(varchar, batch_number) = @SearchString
OR btn = @SearchString;
END
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
