Solved

Convert MS SQL Stored Procedure to a mySQL Stored Procedure

Posted on 2007-11-22
14
1,294 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
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 …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

911 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

17 Experts available now in Live!

Get 1:1 Help Now