SQL Server to MySQL Syntax Differences

I recently converted a large set of procedures from SQL Server 2008 Syntax to MySQL.

For basic conversion of SQL Server stored procedures to MySQL see this post:

http://websitenotebook.blogspot.com/2013/11/converting-sql-server-strored.html

Here are some of the other syntax changes I needed to make to get  my SQL Server stored procedures working on MySQL:

• For getDate() use CurDate() or Now()
• Use Length() instead of len()
• If statements - replace first BEGIN with THEN and END with END IF; use ELSE and ELSEIF in between
• use ifnull instead of isnull; isnull returns take one parameter and returns 1 or 0 to indicate if value is null;
• All the columns in your HAVING clause must be in the SELECT column list
• Instead of TOP N use LIMIT N at the end of your select statement
• SQL Server money data type needs to be changed to decimal with appropriate precision (say decimal(19, 2) where 2 is 2 decimal places) 
• Can't cast to varchar. Use char: CAST(id as CHAR(10))
• MySQL wants your DECLARE statements always at the top of the stored procedure.  
• If trying to get record count use row_count() function: set RecordCount = row_count();
• A procedure with multiple select statements will return the first recordset first when calling a stored procedure through MySQL JDBC driver so make sure the first select statement is the recordset you want the returned or handle multiple recordsets. Use SET instead of SELECT when setting values.
• To exec SQL inside a stored procedure:
SET @s = 'INSERT into mytable select x from other table'
PREPARE stmt1 FROM @s; 
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;  
• Use CONCAT instead of + to concatenate values - and note that if you put a space between CONCAT and the parenthesis won't work:

  CONCAT('%', @email, '%')  

• CASE statement has CASE WHEN ... THEN ... WHEN...THEN...ELSE... END (don't forget THEN and doesn't end with END CASE even though IF end with END IF). Also if you have ELSE CASE WHEN in your code replace with just WHEN
• Instead of UPDATE x=y FROM (join tables) put the join after the update and before the set statement: UPDATE table1 t1 JOIN table2 t2 on t1.pk = t2.fk SET t1.t1_field = t2.t2_field WHERE t1.some_field=some_value
• If using two dashes for comment must have space or tab after the dashes
• Can't do a FULL OUTER JOIN in MySQL but can emulate with a UNION of two left join select statements 
• Temporary tables hang around for the life of a client connection. They aren't automatically dropped at the end of a stored procedure run so you'll have to manually delete them if you want to use the same table again for a particular connected session.
• Can't alter procedure SQL so have to drop and recreate which creates issues with high-availability systems as noted in this blog post (though there may be alternate solutions and in some cases can cause problems in your app anyway if changing signatures - so you'd have to find some alternate to this to coordinate with your app code): https://www.codeofhonor.com/blog/a-better-way-to-update-sql-stored-procedures
• SQL Server lets you get away with having all your joins before all your ON statements and figures out which JOIN goes with which ON. In MySQL you need to put the ON statement right after the JOIN statement to which it refers. 
• Use AUTOINCREMENT instead of IDENTITY creating tables: NewColumn INT IDENTITY(1,1) NOT NULL becomes NewColumn INT NOT NULL AUTO_INCREMENT
• Instead of select returnId = @@identity use SELECT (@@Identity AS id) or SELECT  id = (@@Identity);
• Stored procedures with no parameters need () after procedure name
• You may need to use varbinary(1000) or appropriately length instead of binary data type in stored procedure parameter list if you get truncation error
• Instead of PATINDEX use POSITION or LOCATE
• Can't use keywords as variable names. For example can't use: Declare sql varchar(1000) - you'll have to pick another variable name
• Temporary tables do not have # in front of the name; instead use syntax: create temporary table or drop temporary table;
• Derived tables do not have # in front of name; make sure to use AS when defining the alias: i.e. instead of select... #t select.... as t
• Execute instead of exec; If you are calling one stored procedure inside another use CALL instead of EXEC
• If you see 'expecting end$' in error message when creating a stored procedure make sure you have a begin and end around the lines of SQL within the body of the procedure. 
• Unexpected IDENT_QUOTED: Declare the delimiter for the procedure at the top of the procedure to be something other than semicolon (;). Set the delimiter back to semicolon at the end of the procedure. See DELIMITER in sample code above.
• Use backticks (`) instead of square brackets [] for db objects with spaces in 
  names