Convert MS SQL Stored Procedure to a mySQL Stored Procedure

  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

Try removing dbo. and anything previous to it. I think that should work here.

Hi,

I've tried that, but it complains about the variables at the top

Remove @ from everywhere and then try

It still complains about the variables

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;

Select allOpen in new window

That didnt work either.

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

Select allOpen in new window

How do I get this into mySQL.

I use an app call Navicat, and it doesnt like the Create Procedure

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

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')

Select allOpen in new window

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

I'll stick to dynamic SQL and temporary tables.

Cheers