Solved

Convert MS SQL Stored Procedure to a mySQL Stored Procedure

Posted on 2007-11-22
14
1,303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 25

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
Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

 
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 25

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

728 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