?
Solved

Convert MS SQL Stored Procedure to a mySQL Stored Procedure

Posted on 2007-11-22
14
Medium Priority
?
1,306 Views
Last Modified: 2012-08-14
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

0
Comment
Question by:BenthamLtd
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 20334669
  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
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20334674
Try removing dbo. and anything previous to it. I think that should work here.
0
 

Author Comment

by:BenthamLtd
ID: 20334762
Hi,

I've tried that, but it complains about the variables at the top
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20334784
Remove @ from everywhere and then try
0
 

Author Comment

by:BenthamLtd
ID: 20334901
It still complains about the variables
0
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 20334945
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
 

Author Comment

by:BenthamLtd
ID: 20335268
That didnt work either.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20336088
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
 
LVL 25

Accepted Solution

by:
imitchie earned 2000 total points
ID: 20336091
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
 

Author Comment

by:BenthamLtd
ID: 20336271
How do I get this into mySQL.

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

Expert Comment

by:imitchie
ID: 20336369
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
 

Author Comment

by:BenthamLtd
ID: 20336466
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20336534
>> 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
 

Author Comment

by:BenthamLtd
ID: 20337793
I'll stick to dynamic SQL and temporary tables.

Cheers
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question