---
title: "MS SQL --> MySQL"
type: "forum-thread"
url: "https://www.webforum.nu/amne/databaser-sql/159063-ms-sql-mysql"
topic: "Databaser & SQL"
topic_url: "https://www.webforum.nu/amne/databaser-sql"
author: "CatZ"
published: "2007-04-09T10:28:21.000Z"
updated: "2007-04-09T17:35:56.000Z"
replies: 2
views: 302
page: 1
pages: 1
language: "sv-SE"
site: "webForum — webforum.nu"
rights: "Upphovsrätten till varje inlägg tillhör dess författare."
attribution: "Citera som: webForum, https://www.webforum.nu/amne/databaser-sql/159063-ms-sql-mysql"
---

# MS SQL --> MySQL

## #1 — CatZ, 2007-04-09T10:28Z

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?

Permalänk: https://www.webforum.nu/p/159063

## #2 — Peter S, 2007-04-09T14:19Z

```
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 ;
```

Permalänk: https://www.webforum.nu/p/1957637

## #3 — CatZ, 2007-04-09T17:35Z

Tack ska du ha, det var Coalesce som var mitt storsta problem, skulle precis specificera fragan lite tydligare :)

Det verkar inte vara nagra storre skillnader i funktionalitet sa langt.

Permalänk: https://www.webforum.nu/p/1957712

---

Tråden på webben: https://www.webforum.nu/amne/databaser-sql/159063-ms-sql-mysql