Convert Stored Procedures from SQL Server to MySQL
- Tech
Jessica C. Dills
- 0
- 5 minutes read
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 Server | MySQL |
| CHARINDEX | LOCATE |
| CONTAINS($expression, $template) | $expression LIKE %$template% |
| CONVERT | CAST |
| LEN | LENGTH |
| DATEADD(year, 1, $date) | $date + interval 1 year |
| DATEADD(month, 1, $date) | $date + interval 1 month |
| DATEADD(day, 1, $date) | $date + interval 1 day |
| DATEDIFF | TIMESTAMPDIFF |
| DATEPART(year, $date) | DATE_FORMAT($date, ‘%Y’) |
| DATEPART(month, $date) | DATE_FORMAT($date, ‘%m’) |
| DATEPART(day, $date) | DATE_FORMAT($date, ‘0’) |
| GETDATE | NOW |
| GETUTCDATE | UTC_TIMESTAMP |
| $string1 + $string2 | CONCAT($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
