Running MySQL queries with LIMIT on MS SQL

By
Damien
– January 30, 2011Posted in: SQL

There are some difference in SQL syntax for MySQL and MS SQL. One of the differences is TOP and LIMIT keywords. And this is bad. Because LIMIT comes in the end of query and TOP comes in the beginning. So, simply to replace is not good. I’m missing the ability of LIMIT to use 2 parameters… This example, works only with “LIMIT 10″ -like queries with single number after LIMIT keyword.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

/// Converts MySQL "LIMIT" queries to MS SQL "TOP" queries
/// </summary>
/// <param name="q">Query</param>
/// <returns></returns>
public string MakeQuerySQLServerCompatible(string q)
{
    string res = q;
    Regex limit = new Regex("SELECT(.+)LIMIT (\\d+)", RegexOptions.IgnoreCase | RegexOptions.Singleline);
    Match m = limit.Match(q);
    if (m.Groups.Count == 3)
    {
        string beforeLimit = m.Groups[1].Value;
        string limitCount = m.Groups[2].Value;
        res = string.Format("SELECT TOP ({0}) {1}", limitCount, beforeLimit);
    }
        return res;
    }

The idea is to remove LIMIT keyword and replace it with SELECT TOP statement. Ideally every query should limit amount of rows to avoid performance issues. Using this trick you can support both MySql and MsSql queries. Sometimes we still need to use old ADO instead of ORM.

Tags: