Convert Stored Procedures from SQL Server to MySQL

 Convert Stored Procedures from SQL Server to MySQL

This article covers basic techniques to convert code of Microsoft SQL stored procedure into MySQL format. General knowledge in database management and experience in composing SQL queries are required to understand the whitepaper.

The example below illustrated how MS SQL and MySQL stored procedures are basically distinguished. The structure of stored procedure in SQL Server is complied with the following format:

CREATE PROCEDURE [dbo].[CHECKREFERENCES]

(

@P_ID decimal(12),

@P_CHECKREFERENCES int OUTPUT

)

AS

RETURN

MySQL requires stored procedures format to be as follows:

DELIMITER $$

CREATE PROCEDURE CHECKREFERENCES(

IN P_ID decimal(12),

OUT P_CHECKREFERENCES INT

)

BEGIN

END$$

DELIMITER ;

As you may see, the primary differences are:

  • Since MySQL treats CRLF as statement terminator, it is necessary to define alternate statements delimiter before composing stored procedures or functions
  • Unlike SQL Server, MySQL requires every procedure’s input parameter is declared as ‘IN’
  • MySQL always starts body of stored procedure from ‘BEGIN’ keyword
  • MS SQL default namespace ‘dbo.’ must be removed
  • Sometime MS SQL object names are enclosed in square brackets, in MySQL those symbols are replaced by ` or cut off

According to MySQL syntax, IF-statement must include keyword “THEN” and block terminator “END IF;” when containing more than one statement:

IF condition THEN

statements;

ELSE

else-statements;

END IF;

Error Handling. SQL Server provides system variable @@ERROR to get information about errors below some critical level (that allow stored procedure to continue), while errors above that level terminates execution immediately and returns and error. In MySQL most errors cause stored procedure termination with returning error code. To force MySQL stored procedures act more close to SQL Server, the following error handler should be defined:

DECLARE “@ERROR” INT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQL EXCEPTION

BEGIN

SET “@ERROR” = 1;

END;

And then you can use @ERROR variable inside a stored procedure as follows:

IF “@ERROR” = 0 THEN

/* do something for success */

ELSE

/* process errors */

END IF;

Transactions. SQL Server handles transactions using the following statements:

  • BEGIN TRANSACTION
  • COMMIT TRANSACTION
  • ROLLBACK TRANSACTION

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

Built-in Functions. Conversion of stored procedures from MS SQL to MySQL includes replacing specific embedded functions and operators of SQL Server with MySQL equivalents as it is illustrated by this table:

SQL ServerMySQL
CHARINDEXLOCATE
CONTAINS($expression, $template)$expression LIKE %$template%
CONVERTCAST
LENLENGTH
DATEADD(year, 1, $date)$date + interval 1 year
DATEADD(month, 1, $date)$date + interval 1 month
DATEADD(day, 1, $date)$date + interval 1 day
DATEDIFFTIMESTAMPDIFF
DATEPART(year,    $date)DATE_FORMAT($date, ‘%Y’)
DATEPART(month,    $date)DATE_FORMAT($date, ‘%m’)
DATEPART(day,    $date)DATE_FORMAT($date, ‘0’)
GETDATENOW
GETUTCDATEUTC_TIMESTAMP
$string1 + $string2CONCAT($string1, $string2)

More articles about Microsoft SQL, MySQL and other popular database systems can be found at https://www.convert-in.com/docs/mss2sql/contents.htm

 

Jessica C. Dills

Leave a Reply

Your email address will not be published. Required fields are marked *