Convert MS SQL Stored Procedure to a mySQL Stored Procedure

Hi,

Does anyone know how to convert this MS SQL designed Stored Procedure to work on a mySQL 5 database.
CREATE PROCEDURE default_list3 
 
@QueryID int, 
@Exclude_StartDate Datetime,
@Exclude_EndDate Datetime,
@Include_StartDate Datetime,
@Include_EndDate Datetime
 
AS
 
SELECT     dbo.cart.Customer_URN, dbo.customer.Customer_Title, dbo.customer.Customer_Forename,  dbo.customer.Customer_Surname, OutCall.dbo.ProductCodeList.QueryID
FROM         dbo.cart INNER JOIN
                      dbo.cartitem ON dbo.cart.Cart_URN = dbo.cartitem.Cart_URN INNER JOIN
                      dbo.product ON dbo.cartitem.Product_URN = dbo.product.Product_URN INNER JOIN
                      dbo.customer ON dbo.cart.Customer_URN = dbo.customer.Customer_URN INNER JOIN
                      OutCall.dbo.ProductCodeList ON dbo.product.Product_Code = OutCall.dbo.ProductCodeList.Product_Code
WHERE     (dbo.cart.Cart_Order_DateTime BETWEEN @Include_StartDate AND @Include_EndDate) 
                      AND (dbo.cart.Cart_Status = N'Order')
GROUP BY dbo.cart.Customer_URN, dbo.customer.Customer_Title, dbo.customer.Customer_Forename, dbo.customer.Customer_Surname, 
                      dbo.customer.Customer_Marketing, OutCall.dbo.ProductCodeList.QueryID
HAVING      (NOT (dbo.cart.Customer_URN IN
                          (SELECT     dbo.cart.Customer_URN
                            FROM          dbo.cart INNER JOIN
                                                   dbo.cartitem ON dbo.cart.Cart_URN = dbo.cartitem.Cart_URN INNER JOIN
                                                   dbo.product ON dbo.cartitem.Product_URN = dbo.product.Product_URN
                            WHERE      (dbo.cart.Cart_Order_DateTime BETWEEN @Exclude_StartDate AND @Exclude_EndDate) AND (dbo.cart.Cart_Status = N'ORDER')
                            GROUP BY dbo.cart.Customer_URN))) AND (dbo.customer.Customer_Marketing <> N'DEAD') AND (OutCall.dbo.ProductCodeList.QueryID = @QueryID)
                            ORDER BY dbo.customer.Customer_Surname
GO

Open in new window

BenthamLtdAsked:
Who is Participating?
 
imitchieConnect With a Mentor Commented:
oops, remove dbo. from everything too
CREATE PROCEDURE default_list3 
( 
in QueryID int, 
in Exclude_StartDate Datetime,
in Exclude_EndDate Datetime,
in Include_StartDate Datetime,
in Include_EndDate Datetime
)
AS
 
SELECT     cart.Customer_URN, customer.Customer_Title, customer.Customer_Forename,  customer.Customer_Surname, OutCall.ProductCodeList.QueryID
FROM         cart INNER JOIN
                      cartitem ON cart.Cart_URN = cartitem.Cart_URN INNER JOIN
                      product ON cartitem.Product_URN = product.Product_URN INNER JOIN
                      customer ON cart.Customer_URN = customer.Customer_URN INNER JOIN
                      OutCall.ProductCodeList ON product.Product_Code = OutCall.ProductCodeList.Product_Code
WHERE     (cart.Cart_Order_DateTime BETWEEN Include_StartDate AND Include_EndDate) 
                      AND (cart.Cart_Status = 'Order')
GROUP BY cart.Customer_URN, customer.Customer_Title, customer.Customer_Forename, customer.Customer_Surname, 
                      customer.Customer_Marketing, OutCall.ProductCodeList.QueryID
HAVING      (NOT (cart.Customer_URN IN
                          (SELECT     cart.Customer_URN
                            FROM          cart INNER JOIN
                                                   cartitem ON cart.Cart_URN = cartitem.Cart_URN INNER JOIN
                                                   product ON cartitem.Product_URN = product.Product_URN
                            WHERE      (cart.Cart_Order_DateTime BETWEEN Exclude_StartDate AND Exclude_EndDate) AND (cart.Cart_Status = 'ORDER')
                            GROUP BY cart.Customer_URN))) AND (customer.Customer_Marketing <> 'DEAD') AND (OutCall.ProductCodeList.QueryID = QueryID)
                            ORDER BY customer.Customer_Surname

Open in new window

0
 
Tomas Helgi JohannssonCommented:
  Hi!

Here is an example on how to return a result set in a function
http://www.java2s.com/Code/PostgreSQL/Store-Procedure-Function/Usingtheresultsetreturnedfromthefunction.htm

This would give you an idea how to convert the MS SQL Proc to MySQL Proc

Regards,
  Tomas Helgi
0
 
Ashish PatelCommented:
Try removing dbo. and anything previous to it. I think that should work here.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
BenthamLtdAuthor Commented:
Hi,

I've tried that, but it complains about the variables at the top
0
 
Ashish PatelCommented:
Remove @ from everywhere and then try
0
 
BenthamLtdAuthor Commented:
It still complains about the variables
0
 
Tomas Helgi JohannssonCommented:
Create a table called MyTable which has the same fields as the select statement returns and try the code snippet attached.

Regards,
    Tomas Helgi
CREATE FUNCTION default_list3(int,DateTime,DateTime,DateTime,DateTime) RETURNS myTable AS 
SELECT     dbo.cart.Customer_URN, dbo.customer.Customer_Title, dbo.customer.Customer_Forename,  dbo.customer.Customer_Surname, OutCall.dbo.ProductCodeList.QueryID
FROM         dbo.cart INNER JOIN
                      dbo.cartitem ON dbo.cart.Cart_URN = dbo.cartitem.Cart_URN INNER JOIN
                      dbo.product ON dbo.cartitem.Product_URN = dbo.product.Product_URN INNER JOIN
                      dbo.customer ON dbo.cart.Customer_URN = dbo.customer.Customer_URN INNER JOIN
                      OutCall.dbo.ProductCodeList ON dbo.product.Product_Code = OutCall.dbo.ProductCodeList.Product_Code
WHERE     (dbo.cart.Cart_Order_DateTime BETWEEN $3 AND $4) 
                      AND (dbo.cart.Cart_Status = N'Order')
GROUP BY dbo.cart.Customer_URN, dbo.customer.Customer_Title, dbo.customer.Customer_Forename, dbo.customer.Customer_Surname, 
                      dbo.customer.Customer_Marketing, OutCall.dbo.ProductCodeList.QueryID
HAVING      (NOT (dbo.cart.Customer_URN IN
                          (SELECT     dbo.cart.Customer_URN
                            FROM          dbo.cart INNER JOIN
                                                   dbo.cartitem ON dbo.cart.Cart_URN = dbo.cartitem.Cart_URN INNER JOIN
                                                   dbo.product ON dbo.cartitem.Product_URN = dbo.product.Product_URN
                            WHERE      (dbo.cart.Cart_Order_DateTime BETWEEN $1 AND $2) AND (dbo.cart.Cart_Status = N'ORDER')
                            GROUP BY dbo.cart.Customer_URN))) AND (dbo.customer.Customer_Marketing <> N'DEAD') AND (OutCall.dbo.ProductCodeList.QueryID = @QueryID)
                            ORDER BY dbo.customer.Customer_Surname;

Open in new window

0
 
BenthamLtdAuthor Commented:
That didnt work either.
0
 
imitchieCommented:
try this
CREATE PROCEDURE default_list3 
( 
in QueryID int, 
in Exclude_StartDate Datetime,
in Exclude_EndDate Datetime,
in Include_StartDate Datetime,
in Include_EndDate Datetime
)
AS
 
SELECT     dbo.cart.Customer_URN, dbo.customer.Customer_Title, dbo.customer.Customer_Forename,  dbo.customer.Customer_Surname, OutCall.dbo.ProductCodeList.QueryID
FROM         dbo.cart INNER JOIN
                      dbo.cartitem ON dbo.cart.Cart_URN = dbo.cartitem.Cart_URN INNER JOIN
                      dbo.product ON dbo.cartitem.Product_URN = dbo.product.Product_URN INNER JOIN
                      dbo.customer ON dbo.cart.Customer_URN = dbo.customer.Customer_URN INNER JOIN
                      OutCall.dbo.ProductCodeList ON dbo.product.Product_Code = OutCall.dbo.ProductCodeList.Product_Code
WHERE     (dbo.cart.Cart_Order_DateTime BETWEEN Include_StartDate AND Include_EndDate) 
                      AND (dbo.cart.Cart_Status = 'Order')
GROUP BY dbo.cart.Customer_URN, dbo.customer.Customer_Title, dbo.customer.Customer_Forename, dbo.customer.Customer_Surname, 
                      dbo.customer.Customer_Marketing, OutCall.dbo.ProductCodeList.QueryID
HAVING      (NOT (dbo.cart.Customer_URN IN
                          (SELECT     dbo.cart.Customer_URN
                            FROM          dbo.cart INNER JOIN
                                                   dbo.cartitem ON dbo.cart.Cart_URN = dbo.cartitem.Cart_URN INNER JOIN
                                                   dbo.product ON dbo.cartitem.Product_URN = dbo.product.Product_URN
                            WHERE      (dbo.cart.Cart_Order_DateTime BETWEEN Exclude_StartDate AND Exclude_EndDate) AND (dbo.cart.Cart_Status = 'ORDER')
                            GROUP BY dbo.cart.Customer_URN))) AND (dbo.customer.Customer_Marketing <> 'DEAD') AND (OutCall.dbo.ProductCodeList.QueryID = QueryID)
                            ORDER BY dbo.customer.Customer_Surname

Open in new window

0
 
BenthamLtdAuthor Commented:
How do I get this into mySQL.

I use an app call Navicat, and it doesnt like the Create Procedure
0
 
imitchieCommented:
can you try following the steps in
http://www.navicat.com/linux_manual/SP_Funct.html

create a blank stored procedure first, then edit the content and paste the above
0
 
BenthamLtdAuthor Commented:
Hi,
Got it working.

Pasted the code into MySQL Query Browser and it created it all.

Is it possible to pass multiple values through a variable. I know it cant be done in MSSQL.

Say something like:

(in PartCode varchar)

Call default_list3 ('1890' or '1891')


CREATE PROCEDURE default_list3
(in Exclude_StartDate Datetime,in Exclude_EndDate Datetime,in Include_StartDate Datetime,in Include_EndDate Datetime)                          
SELECT
cart.Customer_URN,
TRIM(customer.Customer_Title) AS Customer_Title,
TRIM(customer.Customer_Forename) AS Customer_Forename,
TRIM(customer.Customer_Surname) AS Customer_Surname
FROM
cart
Inner Join cartitem ON cart.Cart_URN = cartitem.Cart_URN
Inner Join product ON cartitem.Product_URN = product.Product_URN
Inner Join customer ON customer.Customer_URN = cart.Customer_URN
WHERE     cart.Cart_Order_DateTime BETWEEN Include_StartDate AND Include_EndDate
                      AND cart.Cart_Status = 'Order' AND product.Product_Code = '1633' AND customer.Customer_Marketing NOT LIKE  'DEAD' AND
cart.Cart_Order_Group =  '1' AND cart.Customer_URN NOT IN
                          (SELECT     cart.Customer_URN
                            FROM          cart INNER JOIN
                                                   cartitem ON cart.Cart_URN = cartitem.Cart_URN INNER JOIN
                                                   product ON cartitem.Product_URN = product.Product_URN
                            WHERE      cart.Cart_Order_DateTime BETWEEN Exclude_StartDate AND Exclude_EndDate AND cart.Cart_Status = N'ORDER')
                            GROUP BY customer.Customer_URN
ORDER BY Customer_Surname
 
Then execute it like this:
Call default_list3 ('2007-10-15 00:00:00','2007-11-15 00:00:00','2007-01-15 00:00:00','2007-11-15 00:00:00')

Open in new window

0
 
imitchieCommented:
>> I know it cant be done in MSSQL.
that is usually only through
a, dynamic SQL and temporary tables
b, mashed up LIKE statements destroying index usage

the simple case:
CREATE PROCEDURE default_list3
(in CustomerID varchar(100), ... rest

WHERE ',' + CustomerID + ',' LIKE '%,' + Customer.CustomerID + ',%'

where the CustomerID being passed in is a string like '1,2,3'

so your input becomes ,1,2,3,4,
and the matches are ,1,   ,2,   ,3,   ,4, etc
0
 
BenthamLtdAuthor Commented:
I'll stick to dynamic SQL and temporary tables.

Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.