Home > Documentation > MS SQL to MySQL

Convert MS SQL Stored Procedures to MySQL

[SQL Server to MySQL Converter]  [About Migration]  [Converting Queries]  [Safe Types Mapping]

Microsoft SQL and MySQL database management systems have similar syntax of stored procedures. However, there is number of particularities that must be handled properly when migrating procedures source code from MS SQL to MySQL. This article explores such differences.

1. Structure of Code. All major differences between structures of stored procedures in Microsoft SQL and MySQL are illustrated in comparison below. In SQL Server definitions are complied with the following format:

CREATE PROCEDURE name
	arg1 type,
	arg2 type,
        ... 
AS
	statement1
	statement2
        ...
GO

While Microsoft SQL allows to dismiss "BEGIN" and "END" keywords that indicate margins of procedure's body, MySQL requires these keywords are used. So, the stored procedure above must be converted into MySQL format as follows:

CREATE PROCEDURE name (
	arg1 type,
	arg2 type,
	...
)
BEGIN
	statement1;
	statement2;
        ...
END;

2. Passing Parameters. MS SQL and MySQL allows to pass input and output parameters. While SQL Server requires only output parameters to be defined via "OUTPUT" keyword, in MySQL both input and output parameters must be declared via "IN" and "OUT" keywords correspondingly.

For example the following MS SQL declaration:

CREATE PROCEDURE proc1 @inarg1 varchar(40) 
    , @inarg2 integer 
    , @outarg1 integer OUTPUT
    , @outarg2 varchar(40) OUTPUT    

is converted into MySQL equivalent:

CREATE PROCEDURE proc1 (IN inarg1 varchar(40),
    IN inarg2 integer,
    OUT outarg1 integer,
    OUT outarg2 varchar(40))    

3. IF...THEN...ELSE... MySQL requires that an IF-block include "THEN" and that a block with more than one statement is terminated by an "END IF;". Note that the semicolon symbol after "END IF" is required:

IF <expression> THEN
    statement1;
    statement2;
    ...
ELSE
    statement1;
    statement2;
    ...
END IF;

4. IF EXISTS Statements. MS SQL allows to check if query returns some rows via &qout;IF EXIST&qout; statement. For example:

IF EXISTS(SELECT prod_id FROM products WHERE price > 100)
BEGIN
    do something
END

MySQL does not have such conditional operator, that's why it is necessary to use COUNT(*) function instead:

IF (SELECT COUNT(*) FROM products WHERE price > 100) > 0 THEN
    do something
END IF;

5. Clauses in SELECT Statements. While SQL Server allows variables in clauses of SELECT-statements, MySQL requires constants only. This issue can be resolved using PREPARE-statement as follows. Assume there is a query in Microsoft SQL:

SELECT 
	a.subject,
	a.createdate,
	a.views,
	a.user
FROM 
	classifieds a
WHERE
	id = "@id"
LIMIT 
	"@startrow","@maxrows";

It is possible to use the following code to implement SELECT-statements with variable clauses in MySQL:

PREPARE STMT FROM 
	'select a.subject,
		a.createdate,
		a.views,
		a.user
	from classifieds a
	where id = ?
	limit ?,?';

EXECUTE STMT USING "@id","@startrow","@maxrows";

6. Error Handling. Microsoft SQL Server allows errors below a certain level to be returned in @@ERROR and the stored procedure continues, while errors above that level terminates execution immediately and returns and error. In MySQL most errors terminate the stored procedure and return an error code. To get behavior of MySQL stored procedures more close to SQL Server it is necessary to define an error handler like follows:

DECLARE "@ERROR" INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
	SET "@ERROR" = 1;
END;

And then modify code of stored procedures in order to use @ERROR variable instead of @@ERROR. For example:

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;

7. Concatenation of Strings. MS SQL provides concatenation operator as "plus" 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';

8. Transactions. SQL Server uses the following statements to handle transactions:

MySQL equivalents for the same purposes are: START TRANSACTION, COMMIT, ROLLBACK

9. Embedded Functions. Last step of converting MS SQL stored procedures into MySQL format is to replace embedded functions and operators that are specific for Microsoft SQL Server with MySQL equivalents. More details on this topic are available in this article.

Have any questions? Contact us