How to Convert MS SQL Server Stored Procedure Queries to MySQL

by Luke P. Issac on March 9, 2014

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, '|%');

Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 1 comment… read it below or add one }

1 Eric Man March 25, 2014 at 4:38 am

Hey bro,

For the data-type conversion, for SQL Server, was there any reason you chose to use convert? Can’t you also do something like:

DECLARE @int1 int = 1

PRINT CAST(@int1 as VARCHAR(50))

Just wondering if there was any trade-off.

TY!
EM

Leave a Comment

Previous post:

Next post: