DELIMITER $$
DROP PROCEDURE IF EXISTS dbo.spDefaultDisplayOrders $$
CREATE PROCEDURE dbo.spDefaultDisplayOrders
(
in CompanyID int
)
BEGIN
SELECT
tblInvoice.InvoiceID,
tblInvoice.InvoicePrefix + '' + convert(tblInvoice.InvoiceNumber, char(100)) AS InvoiceNumber,
tblCustomers.Name as Customer,
tblInvoice.EndDate,
Coalesce(tblInvoice.KmPrice * tblInvoice.Km, 0) * (1 + tblInvoice.GST) AS KmTotal,
Coalesce(myHourTotal.HourTotal, 0) * (1 + tblInvoice.GST) AS HourTotal,
Coalesce(myProductTotal.ProductTotal, 0) * (1 + tblInvoice.GST) AS ProductTotal,
(Coalesce(tblInvoice.KmPrice * tblInvoice.Km, 0) + Coalesce(myHourTotal.HourTotal, 0) + Coalesce(myProductTotal.ProductTotal, 0)) * (1 +tblInvoice.GST) AS TOTAL
FROM
tblInvoice
LEFT OUTER JOIN
(
SELECT
tblInvoice.InvoiceID,
SUM(dbo.LineTotal(tblInvoiceDetails.Quantity, tblInvoice.HourPrice, 0, tblInvoice.GST, 1)) AS HourTotal
FROM
tblInvoice
LEFT OUTER JOIN tblInvoiceDetails ON tblInvoice.InvoiceID = tblInvoiceDetails.InvoiceID
WHERE
(tblInvoiceDetails.InvoiceID = tblInvoice.InvoiceID)
GROUP BY
tblInvoice.InvoiceID
) AS myHourTotal
ON tblInvoice.InvoiceID = myHourTotal.InvoiceID
LEFT OUTER JOIN
(
SELECT
tblInvoice.InvoiceID,
SUM(dbo.LineTotal(tblProducts.Quantity, tblProducts.UnitPrice, 0, tblInvoice.GST, 1)) AS ProductTotal
FROM
tblInvoice
LEFT OUTER JOIN tblProducts ON tblInvoice.InvoiceID = tblProducts.InvoiceID
WHERE
(tblProducts.InvoiceID = tblInvoice.InvoiceID)
GROUP BY
tblInvoice.InvoiceID
) AS myProductTotal
ON tblInvoice.InvoiceID = myProductTotal.InvoiceID
INNER JOIN tblCustomers ON tblInvoice.CustomerID = tblCustomers.CustomerID
GROUP BY
tblInvoice.InvoiceID,
tblInvoice.InvoicePrefix + '' + convert(tblInvoice.InvoiceNumber, char(100)),
tblCustomers.Name,
tblInvoice.EndDate,
Coalesce(tblInvoice.KmPrice * tblInvoice.Km, 0) * (1 + tblInvoice.GST),
Coalesce(myHourTotal.HourTotal, 0) * (1 + tblInvoice.GST),
Coalesce(myProductTotal.ProductTotal, 0) * (1 + tblInvoice.GST),
(Coalesce(tblInvoice.KmPrice * tblInvoice.Km, 0) + Coalesce(myHourTotal.HourTotal, 0) + Coalesce(myProductTotal.ProductTotal, 0)) * (1 +tblInvoice.GST)
ORDER BY
tblInvoice.InvoiceID;
END $$
DELIMITER ;
Frågan#1
Sitter och försöker förstå hur jag ska utnyttja stored procedures i MySql. Det går inget bra att konvertera! Vilka är de största skillnaderna? Jag försökte med MySql Migration Tool men det ville inte fungera så värst bra.
Till att börja med har jag den här frågan:
ALTER PROCEDURE dbo.spDefaultDisplayOrders
(
@CompanyID int
)
AS
SET NOCOUNT ON;
BEGIN
SELECT
tblInvoice.InvoiceID,
tblInvoice.InvoicePrefix + '' + convert(varchar, tblInvoice.InvoiceNumber) AS InvoiceNumber,
tblCustomers.Name as Customer,
tblInvoice.EndDate,
IsNull(tblInvoice.KmPrice * tblInvoice.Km, 0) * (1 + tblInvoice.GST) AS KmTotal,
IsNull(myHourTotal.HourTotal, 0) * (1 + tblInvoice.GST) AS HourTotal,
IsNull(myProductTotal.ProductTotal, 0) * (1 + tblInvoice.GST) AS ProductTotal,
(IsNull(tblInvoice.KmPrice * tblInvoice.Km, 0) + IsNull(myHourTotal.HourTotal, 0) + IsNull(myProductTotal.ProductTotal, 0)) * (1 +tblInvoice.GST) AS TOTAL
FROM
tblInvoice
LEFT OUTER JOIN
(
SELECT
tblInvoice.InvoiceID,
SUM(dbo.LineTotal(tblInvoiceDetails.Quantity, tblInvoice.HourPrice, 0, tblInvoice.GST, 1)) AS HourTotal
FROM
tblInvoice
LEFT OUTER JOIN tblInvoiceDetails ON tblInvoice.InvoiceID = tblInvoiceDetails.InvoiceID
WHERE
(tblInvoiceDetails.InvoiceID = tblInvoice.InvoiceID)
GROUP BY
tblInvoice.InvoiceID
) AS myHourTotal
ON tblInvoice.InvoiceID = myHourTotal.InvoiceID
LEFT OUTER JOIN
(
SELECT
tblInvoice.InvoiceID,
SUM(dbo.LineTotal(tblProducts.Quantity, tblProducts.UnitPrice, 0, tblInvoice.GST, 1)) AS ProductTotal
FROM
tblInvoice
LEFT OUTER JOIN tblProducts ON tblInvoice.InvoiceID = tblProducts.InvoiceID
WHERE
(tblProducts.InvoiceID = tblInvoice.InvoiceID)
GROUP BY
tblInvoice.InvoiceID
) AS myProductTotal
ON tblInvoice.InvoiceID = myProductTotal.InvoiceID
INNER JOIN tblCustomers ON tblInvoice.CustomerID = tblCustomers.CustomerID
GROUP BY
tblInvoice.InvoiceID,
tblInvoice.InvoicePrefix + '' + convert(varchar, tblInvoice.InvoiceNumber),
tblCustomers.Name,
tblInvoice.EndDate,
IsNull(tblInvoice.KmPrice * tblInvoice.Km, 0) * (1 + tblInvoice.GST),
IsNull(myHourTotal.HourTotal, 0) * (1 + tblInvoice.GST),
IsNull(myProductTotal.ProductTotal, 0) * (1 + tblInvoice.GST),
(IsNull(tblInvoice.KmPrice * tblInvoice.Km, 0) + IsNull(myHourTotal.HourTotal, 0) + IsNull(myProductTotal.ProductTotal, 0)) * (1 +tblInvoice.GST)
ORDER BY
tblInvoice.InvoiceID
END
Först försökte jag kopiera in den i MySQL men det var rätt meningslöst då jag får fel på i princip varenda rad. Någon som kan peka på vad som måste rättas till? Det enklaste vore kanske att börja med en tom procedure?
