How to convert stored procedures from MS SQL Server to MySQL

MS SQL Server to MySQL

​​

When switching from MS SQL to MySQL, apart from data migration, you must also transfer the application code, which is in the database.

Earlier we discussed how to move MS SQL to a MySQL database using the WorkSQL Workbench tool.

Within the migration, it will only convert tables and copy data, but it will not convert triggers, views, and stored procedures. You must manually convert them to a MySQL database.

To perform this conversion manually, you must understand the basic differences between MS SQL and MySQL queries.

During my conversion from Microsoft SQL Server to a MySQL database, I encountered the following operators and MS SQL queries that were not MySQL compatible, and I had to convert them as shown below.

Creating stored procedures. Syntax

The basic syntax for creating stored procedures is different.

MS SQL Stored, procedure creation syntax:

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

for MySQL procedure creation syntax:

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

Time table creation

In MS SQL code, I have created several temporary tables that are required to apply. The syntax for creating a temporary table is different, as shown below.

MS SQL syntax for creating a temporary table:

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 syntax for creating a temporary table:

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

IF syntax

I used many conditions in my stored procedures and triggers that didn’t work after conversion to MySQL because the syntax is different as shown below.

MS SQL condition IF Syntax:

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

MySQL condition IF Syntax:

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

IF EXIST status

Another common use, if a condition, is to check whether the query returns any lines or not; and if it returns multiple lines, do something. To do this, I used IF EXISTS in MS SQL, which must be converted to MySQL by the IF command as described 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 is higher, using when the condition is met:

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

Date functions

Using data functions within a stored procedure is quite common. The following table shows the differences between MS SQL and MySQL data, related functions.

Announcement of variables

In MS SQL stored procedures, you can declare variables somewhere between “Begin” and “end”.

However, in MySql, you will have to declare them only after you declare the stored “begin” procedure. A declaration of a variable at any point between is not allowed.

Select the first N records

In MS SQL, you will use SELECT, TOP if you want to select only the first few records. For example, to select the 1st 10 records, you will do the following:

SELECT TOP 10 * FROM TABLE;

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

SELECT * FROM TABLE LIMIT 10;

Converting an integer number to Char

In MS SQL you will perform the following steps (Convert functions) to convert an integer to a character.

CONVERT(VARCHAR(50), someIntVal)

In MySQL, you will use the CAST function to convert an integer to a character, as shown below.

CAST( someIntVal as CHAR)

Concatenation operator

If you manipulate a lot of data inside a stored procedure, you can use some string concatenation execution.

In MS SQL the concatenation operator + character. An example of such usage is shown below.

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

In MySQL, if you use the AnSi mode, it is the same as in MS SQL. i.e. + character will work for concatenation.

But, in the default MySQL mode, we have to use the CONCAT function (“str1”, “str2”, “str3″… “strN”).

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

Enteros

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.