How to Convert MS SQL Server Stored Procedure Queries to MySQL

When you migrate from MS SQL to MySQL, apart from migrating the data, you should also migrate the application code that resides in the database.
Earlier we discussed how to migrate MS SQL to MySQL database using the WorkSQL Workbench tool.
As part of the migration, it will only convert tables and copy the data, but it will not convert triggers, views and stored procedures. You have to manually convert these over to MySQL database.

To perform this manual conversion, you need to understand the key differences between MS SQL and MySQL queries.
During my conversion from Microsoft SQL Server to MySQL database, I encountered the following MS SQL statements and queries, which was not compatible with MySQL and I have to convert them as shown below.

1. Stored Procedure Creation Syntax

The basic stored procedure creation syntax itself is different.
MS SQL Stored Procedure creation syntax:

CREATE PROCEDURE [dbo].[storedProcedureName]
@someString VarChar(150)
As
BEGIN
  --  Sql queries goes here 
END

MySQL Stored Procedure creation syntax:

CREATE PROCEDURE storedProcedureName( IN someString VarChar(150) )
BEGIN
  -- Sql queries goes here
END

2. Temporary Table Creation

In my MS SQL code, I’ve created few temporary tables that are required by the application. The syntax for temporary table creation differs as shown below.
MS SQL temporary table creation syntax:

CREATE TABLE #tableName( 
emp_id VARCHAR(10)COLLATE Database_Default PRIMARY KEY, 
emp_Name VARCHAR(50) COLLATE Database_Default, 
emp_Code VARCHAR(30) COLLATE Database_Default, 
emp_Department VARCHAR(30) COLLATE Database_Default
)

MySQL temporary table creation syntax:

CREATE TEMPORARY TABLE tableName(
emp_id VARCHAR(10),
emp_Name VARCHAR(50),
emp_Code VARCHAR(30),
emp_Department VARCHAR(30)
);

3. IF Condition

I’ve used lot of IF conditions in my stored procedures and triggers, which didn’t work after the conversion to MySQL, as the syntax is different as shown below.
MS SQL IF condition syntax:

if(@intSomeVal='')
BEGIN
 SET @intSomeVal=10
END

MySQL IF condition syntax:

IF @intSomeVal='' THEN
  SET @intSomeVal=10;
END IF;

4. IF EXIST Condition

Another common use of if condition is to check whether a query returned any rows or not; and if it returns some rows, do something. For this, I used IF EXISTS in MS SQL, which should be converted to MySQL IF command as explained below.
MS SQL IF EXITS Example:

IF EXISTS(SELECT 1 FROM #tableName WITH(NOLOCK) WHERE ColName='empType' ) 
BEGIN
  --  Sql queries goes here
END

MySQL equivalent of the above using IF condition:

IF(SELECT count(*) FROM tableName WHERE ColName='empType') > 0  THEN
  --  Sql queries goes here
END IF;

5. Date Functions

Using data functions inside stored procedure is pretty common. The following table gives the difference between MS SQL and MySQL data related functions.

MS SQL Server	MySQL Server
GETDATE( )	NOW( ) SYSDATE( ) CURRENT_TIMESTAMP( )
GETDATE( ) + 1	NOW( ) + INTERVAL 1 DAY CURRENT_TIMESTAMP +INTERVAL 1 DAY
DATEADD(dd, -1, GETDATE())	ADDDATE(NOW(), INTERVAL -1 DAY)
CONVERT(VARCHAR(19),GETDATE())	DATE_FORMAT(NOW(),’%b %d %Y %h:%i %p’)
CONVERT(VARCHAR(10),GETDATE(),110)	DATE_FORMAT(NOW(),’%m-%d-%Y’)
CONVERT(VARCHAR(24),GETDATE(),113)	DATE_FORMAT(NOW(),’%d %b %Y %T:%f’)
CONVERT(VARCHAR(11),GETDATE(),6)	DATE_FORMAT(NOW(),’%d %b %y’)

6. Declare Variables

In MS SQL stored procedure, you can declare variables anywhere between “Begin” and “end”.
However in MySql you will have to declare it just after the stored procedure’s “begin” statement. Declaration of the variable anywhere in between is not allowed.

7. Select First N Rows

In MS SQL, you’ll be using SELECT TOP” when you want to select only first few records. For example, to select 1st 10 records, you’ll do the following:

SELECT TOP 10 * FROM TABLE;

In MySQL, you’ll have to use LIMIT instead of TOP as shown below.

SELECT * FROM TABLE LIMIT 10;

8. Convert Integer to Char

In MS SQL you’ll do the following (CONVERT function) to convert integer to char.

CONVERT(VARCHAR(50),  someIntVal)

In MySQL, you’ll use CAST function to convert integer to char as shown below.

CAST( someIntVal as CHAR)

9. Concatenation Operator

If you are manipulating lot of data inside your stored procedure, you might be performing some string concatenation.
In MS SQL the concatenation operator is + symbol. An example of this usage is shown below.

SET @someString = '%|' + @someStringVal + '|%'

In MySQL if you are using ansi mode, it is same as MS SQL. i.e + symbol will work for concatenation.
But, in the default mode, in MySQL, we need to use CONCAT( “str1″, “str2″, “str3″.. “strN”) function.

SET someString = CONCAT('%|', someStringVal, '|%');