≡ Menu

How to Convert MS SQL Server Stored Procedure Queries to MySQL

When you migrate from MS SQL to MySQL, apart from migrating the data, you should also migrate the application code that resides in the database.

Earlier we discussed how to migrate MS SQL to MySQL database using the WorkSQL Workbench tool.

As part of the migration, it will only convert tables and copy the data, but it will not convert triggers, views and stored procedures. You have to manually convert these over to MySQL database.

To perform this manual conversion, you need to understand the key differences between MS SQL and MySQL queries.

During my conversion from Microsoft SQL Server to MySQL database, I encountered the following MS SQL statements and queries, which was not compatible with MySQL and I have to convert them as shown below.

1. Stored Procedure Creation Syntax

The basic stored procedure creation syntax itself is different.

MS SQL Stored Procedure creation syntax:

CREATE PROCEDURE [dbo].[storedProcedureName]
@someString VarChar(150)
As
BEGIN
  --  Sql queries goes here 
END

MySQL Stored Procedure creation syntax:

CREATE PROCEDURE storedProcedureName( IN someString VarChar(150) )
BEGIN
  -- Sql queries goes here
END

2. Temporary Table Creation

In my MS SQL code, I’ve created few temporary tables that are required by the application. The syntax for temporary table creation differs as shown below.

MS SQL temporary table creation syntax:

CREATE TABLE #tableName( 
emp_id VARCHAR(10)COLLATE Database_Default PRIMARY KEY, 
emp_Name VARCHAR(50) COLLATE Database_Default, 
emp_Code VARCHAR(30) COLLATE Database_Default, 
emp_Department VARCHAR(30) COLLATE Database_Default
)

MySQL temporary table creation syntax:

CREATE TEMPORARY TABLE tableName(
emp_id VARCHAR(10),
emp_Name VARCHAR(50),
emp_Code VARCHAR(30),
emp_Department VARCHAR(30)
);

3. IF Condition

I’ve used lot of IF conditions in my stored procedures and triggers, which didn’t work after the conversion to MySQL, as the syntax is different as shown below.

MS SQL IF condition syntax:

if(@intSomeVal='')
BEGIN
 SET @intSomeVal=10
END

MySQL IF condition syntax:

IF @intSomeVal=''	THEN
  SET @intSomeVal=10;
END IF;

4. IF EXIST Condition

Another common use of if condition is to check whether a query returned any rows or not; and if it returns some rows, do something. For this, I used IF EXISTS in MS SQL, which should be converted to MySQL IF command as explained below.

MS SQL IF EXITS Example:

IF EXISTS(SELECT 1 FROM #tableName WITH(NOLOCK) WHERE ColName='empType' ) 
BEGIN
  --  Sql queries goes here
END

MySQL equivalent of the above using IF condition:

IF(SELECT count(*) FROM tableName WHERE ColName='empType') > 0 	THEN
  --  Sql queries goes here
END IF;

5. Date Functions

Using data functions inside stored procedure is pretty common. The following table gives the difference between MS SQL and MySQL data related functions.

MS SQL Server MySQL Server
GETDATE( ) NOW( )
SYSDATE( )
CURRENT_TIMESTAMP( )
GETDATE( ) + 1 NOW( ) + INTERVAL 1 DAY
CURRENT_TIMESTAMP +INTERVAL 1 DAY
DATEADD(dd, -1, GETDATE()) ADDDATE(NOW(), INTERVAL -1 DAY)
CONVERT(VARCHAR(19),GETDATE()) DATE_FORMAT(NOW(),’%b %d %Y %h:%i %p’)
CONVERT(VARCHAR(10),GETDATE(),110) DATE_FORMAT(NOW(),’%m-%d-%Y’)
CONVERT(VARCHAR(24),GETDATE(),113) DATE_FORMAT(NOW(),’%d %b %Y %T:%f’)
CONVERT(VARCHAR(11),GETDATE(),6) DATE_FORMAT(NOW(),’%d %b %y’)

6. Declare Variables

In MS SQL stored procedure, you can declare variables anywhere between “Begin” and “end”.

However in MySql you will have to declare it just after the stored procedure’s “begin” statement. Declaration of the variable anywhere in between is not allowed.

7. Select First N Rows

In MS SQL, you’ll be using SELECT TOP” when you want to select only first few records. For example, to select 1st 10 records, you’ll do the following:

SELECT TOP 10 * FROM TABLE;

In MySQL, you’ll have to use LIMIT instead of TOP as shown below.

SELECT * FROM TABLE LIMIT 10;

8. Convert Integer to Char

In MS SQL you’ll do the following (CONVERT function) to convert integer to char.

CONVERT(VARCHAR(50),  someIntVal)

In MySQL, you’ll use CAST function to convert integer to char as shown below.

CAST( someIntVal as CHAR)

9. Concatenation Operator

If you are manipulating lot of data inside your stored procedure, you might be performing some string concatenation.

In MS SQL the concatenation operator is + symbol. An example of this usage is shown below.

SET @someString = '%|' + @someStringVal + '|%'

In MySQL if you are using ansi mode, it is same as MS SQL. i.e + symbol will work for concatenation.

But, in the default mode, in MySQL, we need to use CONCAT( “str1”, “str2”, “str3”.. “strN”) function.

SET someString = CONCAT('%|', someStringVal, '|%');
Add your comment

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

Comments on this entry are closed.

  • Eric Man March 25, 2014, 4:38 am

    Hey bro,

    For the data-type conversion, for SQL Server, was there any reason you chose to use convert? Can’t you also do something like:

    DECLARE @int1 int = 1

    PRINT CAST(@int1 as VARCHAR(50))

    Just wondering if there was any trade-off.

    TY!
    EM

  • sravanthi October 17, 2014, 12:44 am

    please convert my stored procedure to mysql

    CREATE procedure [dbo].[AdminPanel_AddGroupsToSession]
    @AppInstanceid int,
    @Ids xml
    as
    begin

    SELECT
    a.b.value(‘GroupId[1]’,’int’) AS GroupId
    into #GroupIds FROM @Ids.nodes(‘DocumentElement/Table1′) a(b)

    select distinct pg.ID,pg.Groupname from ParticipantGroup pg
    inner join #GroupIds g on pg.id = G.GroupId
    inner join AppInstanceGroup aig on pg.ID=aig.GroupId
    where aig.AppInstanceId=@AppInstanceid

    select pgd.loginid,email,pg.Groupname into #UserLogins from participantgroupdetail pgd
    inner join #GroupIds g on pgd.ParticipantGroupid = G.GroupId
    inner join ParticipantGroup pg on pg.ID=pgd.ParticipantGroupid
    inner join Users u on u.LoginID = pgd.LoginID
    WHERE pgd.LoginID not in(select loginid from appsession where appinstanceid=@appinstanceid)

    Declare @Id int

    While ((Select Count(*) From #GroupIds) > 0)
    begin
    Select Top 1 @Id = GroupId From #GroupIds

    IF NOT EXISTS(SELECT Id FROM AppInstanceGroup WHERE AppInstanceId = @AppInstanceId AND GroupId = @Id)
    BEGIN
    INSERT INTO AppInstanceGroup(AppInstanceId,GroupId) VALUES(@AppInstanceId,@Id)
    END

    Delete #GroupIds Where GroupId = @Id
    end

    insert into appsession(appinstanceid,loginid,SessionNumber,status,CreatedDate,LastModifiedOn)
    select @AppInstanceid,Loginid,1,1,GETUTCDATE(),GETUTCDATE() from #UserLogins

    declare @Status int
    select @Status=status from AppInstance where Id = @AppInstanceId

    if(@Status = 5)
    begin

    declare @strLoginId varchar(1000)
    select @strLoginId = coalesce(@strLoginId+’,’,”) + CONVERT(varchar(36), Loginid) from #UserLogins

    if(@strLoginId ”)
    begin
    exec [AdminPanel_SendEmail_ForAppSession] @AppInstanceId,@strLoginId
    end
    end

    drop table #GroupIds
    drop table #UserLogins
    end