Converting SQL Server Stored Procedures to MySQL

I ran the MySQL migration tool to import my tables and data into MySQL from SQL server as noted in a prior post. It went decently well.

The conversion of stored procedures was not quite as simple. Here are some modifications I needed to make to the output script from the conversion tool:
 

• Remove 'or Replace' after CREATE (see drop/create syntax below)
• Remove AS (Views require AS, procedures don't
• Put parentheses around parameter list
• Remove @ from parameter names
• Add IN, OUT, etc. to parameter list
• Set the delimiter before to something after than semicolon and then back to semicolon 
• Semicolon after statements within the stored procedure 
• Remove 'set nocount on' 
• Remove default values for parameters (not supported) 
• Declare a delimiter which is used like GO (e.g. DELIMITER $$ and put $$ where you would normally put GO)

So for example this procedure generated by the migration tool (which won't compile):


----------------------------------------------------------------------------
Routine mydb.SP_CUSTOMER
----------------------------------------------------------------------------
DELIMITER $$

USE `mydb`$$
 CREATE or REPLACE PROCEDURE mydb.SP_CUSTOMER

@id int

AS

 Select * from mydb.customer WHERE customer_id = @id

$$

DELIMITER ;


Becomes this


/*****************************************
Routine mydb.SP_CUSTOMER
*****************************************/
DELIMITER $$

USE `raddata`$$
CREATE PROCEDURE mydb.SP_CUSTOMER

(IN id int)

Select * from mydb.customer WHERE customer_id = id;

$$

DELIMITER ;

Syntax for Altering Stored Procedures

DROP PROCEDURE IF EXISTS foo;
    delimiter $$
    create PROCEDURE foo (args)
    begin
      blah
    end$$
    delimiter ;

Deciphering Error messages
 
First of all if you're looking for error messages in the output you'll often find a generic error message like:

Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near [...code here]

To get a closer clue as to what the problem is, if you are using MySQL Workbench there's a red symbol next to the line that has the problem and the error code is underlined. The red symbol does nothing. However over the text underlined in red and a more descriptive (possibly still not helpful) box pops up.

Other MySQL and SQL Server Syntax Differences

For some additional syntax differences between MySQL and SQL Server check out this post:

http://websitenotebook.blogspot.com/2013/12/sql-server-to-mysql-syntax-differences.html