Solved

Convert MS SQL Stored Procedure to a mySQL Stored Procedure

Posted on 2007-11-22
14
1,291 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 24

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

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

'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 …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now