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
Solved

Convert MS SQL Stored Procedure to a mySQL Stored Procedure

Posted on 2007-11-22
14
1,301 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 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 57
CLI command keep running after close 7 36
Uploading a CSV Data Import via PHP & MySql 3 29
IIF in access query 19 21
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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 …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

791 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