Migration of stored procedures and functions from MS SQL to MySQL

February 1, 2022 By Rose Gold Tech

Following the trend of shifting from proprietor software to open-source equivalents, many companies and organizations run database migration from Microsoft SQL to MySQL. One of the most complicated steps of the migration procedure is conversion of stored procedures and functions according to syntax of the target database management system.

Although MS SQL and MySQL have similar syntax of stored procedures, there are differences that must be processed properly during migration. This article explores those differences and explains how to handle it.

Structure of Code. SQL Server accepts procedure definitions in the following format:

CREATE PROCEDURE name

arg1 type,

arg2 type,

…

statement1

statement2

…

Unlike Microsoft SQL, MySQL requires procedure body to be enclosed betweenBEGIN and ENDkeywords. So, the stored procedure given as example above must be converted into MySQL format as follows:

CREATE PROCEDURE name (

arg1 type,

arg2 type,

…

)

BEGIN

statement1;

statement2;

…

END;

Passing Parameters.Stored procedures in MS SQL and MySQL accept input and output parameters. SQL Server requires special declaration for output parameters only using keyword OUTPUT, but in MySQL both input and output parameters must be specified withIN and OUT keywords.

IF…THEN…ELSE… Statements. Unlike SQL Server, MySQL requires IF-blocks include THEN keyword and to be finished with END IF; when include more than one statement. Pay attention at the semicolon symbol after END IF, it is required:

IF (expression) THEN

statement1;

statement2;

…

ELSE

statement1;

statement2;

…

END IF;

IF EXISTS Statements. SQL Server provides statement IF EXIST to check whether query returns any rows. For example:

IF EXISTS(SELECT prod_id FROM products WHERE price > 100)

BEGIN

do something

END

MySQL does not support the statement, it must be converted into COUNT(*) function:

IF (SELECT COUNT(*) FROM products WHERE price > 100) > 0 THEN

do something

END IF;

Clauses in SELECT Statements.MS SQL accepts variables in clauses of SELECT-statements, but MySQL requires constants only. The work around is to use PREPARE-statement as it is illustrated on example of the following SQLServer query:

SELECT

a.subject,

a.createdate,

a.views,

a.user

FROM

classifieds a

WHERE

id = “@id”

LIMIT

“@startrow”,”@maxrows”;

The same SELECT-statement with variable clauses can be implemented in MySQL through the following code:

PREPARE STMT FROM

‘select a.subject,

a.createdate,

a.views,

a.user

from classifieds a

where id = ?

limit ?,?’;

EXECUTE STMT USING

“@id”,”@startrow”,”@maxrows”;

Error Handling.Technique of handling errors in SQL Server receives errors below a certain level in system variable @@ERROR while the stored procedure continues. At the same time errors above that level terminates execution immediately and returns the error code. MySQL terminates stored procedures in case of any error and return the corresponding code. To force MySQL error handling act similar toSQL Server it is necessary to define custom error handler as follows:

DECLARE “@ERROR” INT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

BEGIN

SET “@ERROR” = 1;

END;

And the source code of stored procedures must be modified to use @ERROR variable instead of @@ERROR. For example, the original code:

IF @@ERROR <>0 GOTO ERROUT

UPDATE MessageBoardEntries SET ReplyCount = ReplyCount – 1

WHERE EntryID = @EntryID

IF @@ERROR <>0 GOTO ERROUT

UPDATE MessageBoardCategories SET PostCount = PostCount -1

WHERE CategoryID = @CategoryID

IF @@ERROR <>0 GOTO ERROUT

must be replaced with:

DECLARE “@ERROR” INT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

BEGIN

SET “@ERROR” = 1;

END;

INSERT groupmessageboardreplies (

parentid,

authorid,

body )

VALUES (

“@entryid”,

“@authorid”,

“@body” );

IF “@ERROR” = 0 THEN

UPDATE groupmessageboardentries

set replycount = replycount + 1,

lastpostdate = NOW(),

lastposter = “@authorid”

WHERE entryid = “@entryid” ;

END IF

IF “@ERROR” = 0 THEN

UPDATE groupmessageboards

set lastpostdate = NOW(),

postcount = postcount + 1,

lastposterid = “@authorid”,

lastpostentryid = “@entryid”

WHERE groupid = “@groupid” ;

END IF;

StringsConcatenation.MS SQL provides concatenation operator as ‘+’ sign:

SET @result = ‘||’ + @col_value + ‘||’

In default configuration MySQL provides CONCAT() function for the same purpose:

SET result = CONCAT(‘||’, col_value, ‘||’);

Also, MySQL can be run in ANSI mode that supports the same syntax of string concatenation as Microsoft SQL. To set ANSI mode either run mysqld with “–ansi’ option or execute these two statements:

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET GLOBAL sql_mode = ‘ANSI’;

SELECT Into Variables.SQL Server allows selecting query results into local variables through the following syntax:

SELECT @v1 = col1, @v2 = col2 FROM …

For the same purpose MySQL uses the syntax of SELECT queries as follows:

SELECT col1, col2 INTO v1, v2 FROM …

Dynamic SQL.MS SQL provides operator EXEC(@var)to execute dynamic SQL queries through. In MySQL it can be done using the following code:

SET @sess_v = var;

PREPARE stmt FROM @sess_v;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

Transactions.MicrosoftSQL provides several statements to handle transactions: