Here is the proc that I am using. You suggest either 2 separate queries or
2 procs?
create proc spreadInvoice
@InvoiceNumber int, /*Provide either Invoice Number or both
*/
@PeachTreeInvoice char(21), /* Peachtree Invoice Number and */
@PeachtreeKey char(21) /* Peachtree Key */
as
/* Procedure to read Customer information by either CustomerKey or
PeachtreeKey
*/
SELECT ........
FROM tabInvoicedItems LEFT OUTER JOIN
tabOrderedItems ON tabInvoicedItems.Line =
tabOrderedItems.Line AND
tabInvoicedItems.OrderNumber =
tabOrderedItems.OrderNumber LEFT OUTER
JOIN
tabItems ON tabInvoicedItems.Item_FK =
tabItems.Item_PK LEFT OUTER JOIN
tabItemTypes ON tabItems.ItemType_FK =
tabItemTypes.ItemType_PK LEFT
OUTER JOIN
tabCustomers INNER JOIN
tabOrders ON tabCustomers.Customer_PK =
tabOrders.Customer_FK ON
tabOrderedItems.OrderNumber = tabOrders.OrderNumber RIGHT OUTER JOIN
tabPeople tabPeople_1 RIGHT OUTER JOIN
tabInvoices LEFT OUTER JOIN
tabPeople ON tabInvoices.EnteredBy_FK =
tabPeople.Person_PK ON
tabPeople_1.Person_PK = tabInvoices.ChangedBy_FK ON
tabInvoicedItems.InvoiceNumber =
tabInvoices.InvoiceNumber
WHERE ((@InvoiceNumber IS NOT NULL) AND (tabInvoices.InvoiceNumber
[EMAIL PROTECTED]))
OR ((@PeachtreeKEY IS NOT NULL) AND (@PeachtreeInvoice IS
NOT NULL)
AND (tabInvoices.PeachtreeInvoiceNumber=
@PeachtreeInvoice)
AND (tabCustomers.Peachtree_FK= @PeachtreeKey))
ORDER BY tabInvoices.InvoiceNumber, tabInvoicedItems.Line;
GO
-----Original Message-----
From: Jochem van Dieten
Andy Ousterhout wrote:
> Lets say that I need to look up an invoice by 2 different mechanisms:
> Internal reference/Key
> 2 strings
>
> Will the execution plan for a stored proc for the Key be different enough
from
> one for the strings to justify creating 2 stored procs? Right now I've
got a
> single stored proc that does both.
The execution plans will be different enough to justify creating
2 execution plans. This requires 2 queries, but depending on your
database you might be able to put multiple queries in one stored
procedure.
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware: a new and convenient web-based time tracking application. Start
tracking and documenting hours spent on a project or with a client with Logware
today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191303
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
