Convert Stored Procedures from SQL Server to MySQL

SQL Server 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 SQL Server 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. SELECT INTO. SQL Server uses the following syntax of selecting query results into local variables:

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

MySQL uses the syntax below for the same purpose:

SELECT col1, col2 INTO v1, v2 FROM ...

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

SET @sess_v = var;
PREPARE stmt FROM @sess_v;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

10.Table-valued Functions. In SQL Server user-defined functions can accept and return table data type that is used to store a set of rows. MySQL does not support this feature, however there is workaround for such cases.

If T-SQL function accept a table-valued argument as input, it must be rewritten for MySQL to accept table name instead of table itself. After that, it can be used to extract necessary data via dynamic queries. For instance, we have a T-SQL function like this:

CREATE TYPE [dbo].[OrderMemberships] AS TABLE(
  [MembershipId] [int] NOT NULL,
  [OrderID] [int] NOT NULL,
  [Price] [money] NOT NULL
)

CREATE PROCEDURE [dbo].[OrderMembershipsCreate]
  @Memberships OrderMembershipsCreateType READONLY,
  @MembershipId INT
AS
  ...
  INSERT INTO OrdersHistory
  SELECT M.OrderID, M.Price, GETDATE() AS Created 
  FROM @Memberships M
  WHERE M.MembershipID = @MembershipId
  ...
END

This function can be migrated into MySQL as follows:

CREATE PROCEDURE OrderMembershipsCreate(
  p_memberships varchar(100), 
  p_membershipid int
)
BEGIN
  DECLARE v_qry varchar(512);
  SET v_qry = CONCAT(
    'insert into OrdersHistory 
     select M.OrderID, M.Price, now() AS Created 
     from ',
    p_memberships,
    ' M where M.membershipid = ',
    p_membershipid
  );	
  SET @sess_v = v_qry;
  PREPARE stmt FROM @sess_v;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;

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

• BEGIN TRANSACTION / TRAN - begin transaction
• COMMIT TRANSACTION / TRAN - commit transaction
• ROLLBACK TRANSACTION / TRAN - rollback transaction

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

12. System Functions. Last step of converting MS SQL stored procedures into MySQL format is to replace system functions and operators that are specific for SQL Server by MySQL equivalents. More details on this topic are available in this article.

Have any questions? Contact us