There comes a time in every developer’s life when he/she must consider switching database engines due to requests from clients or due to an epiphany he/she receives from the higher powers that be. Personally it’s very early in the morning when I wake up that’s when I have epiphanies. Back to the topic at hand, one can convert the database tables easily using automated tools. Other features such as views and stored procedures are a bit tricky due to the way database vendors implement such features. This article equips you with the necessary skills that you need when making the big switch from MS SQL server to MySQL. You can also do this if you want your applications to support multiple database engines (talk of making the most out of ORM frameworks that support multiple database engines). Topics covered in this article
  • Factors to consider when converting MS SQL server stored procedures to MySQL
  • How to create stored procedures in MySQL
  • Converting customer orders case study MS SQL server stored procedures (basic procedures)
  • Summary

Factors to consider when converting MS SQL server stored procedures to MySQL

It is said that if you know MS SQL Server and know MySQL, you will not be imperiled in a hundred database conversions; if you do not know MySQL but do know MS SQL Server, you will win one and lose one; if you do not know MS SQL Server nor MySQL, you will be imperiled in every single database.
Heed the words of the wise and get to know basic stored procedures in MS SQL Server by reading this article Complete Beginner’s guide to SQL Server Stored Procedures with real world examples. There is a 79.3% chance you will be serenaded. In a nutshell, the article has four (4) stored procedures for SELECT, INSERT, UPDATE, and DELETES statements. We will convert these four (4) stored procedures to MySQL. The case study database scripts and data dictionary can be downloaded from the following link mysql_basic_stored_procedures

Customer Ordering Database ERD, Data Dictionary and Database Scripts

The following table shows the differences between MS SQL Server and MySQL
Sr NoItemMS SQL ServerMySQLDid you notice?
1Create stored procedureCREATE PROCEDURE name [parameters]ASBEGIN{SQL STATEMENTS}END BEGINCREATE PROCEDURE name ( [parameters])BEGIN{SQL STATEMENTS}ENDThe parameters for MySQL are in brackets.MySQL does not use the AS keyword
2If… then control structureIF condition{STATEMENTS}ELSE{STATEMENTS}IF condition THEN{STATEMENTS}ELSE{STATEMENTS}END IF (Reminds me of the good old days of VB 6.0)Did you notice the THEN statement after the condition in MySQL?What else did you notice, use the comments section below
3Input parameters in statementsSQL STATEMENT WHERE id = @par_idSQL STATEMENTS WHERE id = par_idUse the comments identify the difference
More will be in the next article on advanced concepts in MySQL stored procedures.

How to create stored procedures in MySQL

[sql] DELIMITER | CREATE PROCEDURE `upProcedureName` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT 'The humble notes that remind us of why we did what we did' BEGIN SQL STATEMENTS END| [/sql] HERE,
  • “DELIMITER |” the delimiter is used to group the procedure statements together. If a semi-colon is used then MySQL will treat the script as having separate statements and generates an error.
  • “CREATE PROCEDURE…” key words used to define the procedure
  • “LANGUAGE SQL” specifies that the language to be used. Allegedly speaking, you can use a different language within MySQL.
  • “DETERMINISTIC” given the same input, the stored procedure will always return the same results
  • “SQL SECURITY DEFINER” security settings, to be covered in the next article on advanced concepts.
  • “COMMENT” used to comment stored procedures
  • “BEGIN…END |” used to define the body of the stored procedure. The horizontal bar is used to terminate the delimiter.

Converting customer orders case study MS SQL server stored procedures (basic procedures) to MySQL

We will convert the following procedures using the above syntax
  1. upProductsLookUp
  2. upProductsAddNew
  3. upProductsUpdateQuantity
  4. upProductsDelete

upProductsLookUp script

[sql] DELIMITER | CREATE PROCEDURE `upProductsLookUp` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT 'Return all the products' BEGIN SELECT * FROM products; END| [/sql] Calling the stored procedure [sql] call upProductsLookUp(); [/sql]

upProductsAddNew script

[sql] DELIMITER | CREATE PROCEDURE `upProductsAddNew` (IN var_product_name varchar(145), IN var_description varchar(250)) LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT 'Add a new  product' BEGIN INSERT INTO products (product_name,description) VALUES (var_product_name,var_description); END| [/sql] Calling the stored procedure [sql] call upProductsAddNew ('Samsung','Android smart phone'); [/sql]

upProductsUpdateQuantity script

[sql] DELIMITER | CREATE PROCEDURE `upProductsUpdateQuantity` (IN var_product_id int, IN var_quantity int) LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT 'Update the quantity at hand' BEGIN UPDATE products SET quantity_at_hand = quantity_at_hand + var_quantity WHERE product_id = var_product_id; END| [/sql] Calling the stored procedure [sql] call upProductsUpdateQuantity(1,23); [/sql]

upProductsDelete script

[sql] DELIMITER | CREATE PROCEDURE `upProductsDelete` (IN var_product_id int) LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT 'Delete a product' BEGIN DELETE FROM products WHERE product_id = var_product_id; END| [/sql] Calling the stored procedure [sql] call upProductsDelete(1); [/sql]

Summary

Converting MS SQL server stored procedures to MySQL is not rocket science for those who know what they are doing. The major things you need to focus on are;
  1. Include the delimiter
  2. Change the create procedure body
  3. Include the change the parameters syntax by removing @ symbols and including IN or OUT keywords.
  4. Match begin and end block, and if… then and other control structures that you may use to those of MySQL
  5. Copy and paste your raw SQL statements and make minimal modifications on the variables used
We have won the battle but not the war yet, subscribe to our newsletter to get updates delivered to your mailbox when we publish the article on advanced concepts in MySQL stored procedure and together, we can win the stored procedures war.