mysql中DAL函数的存储过程转换

GetCitiesByLocation 看起来像这样

public DataTable GetCitiesByLocation(long pStateID, string pKeyword)
{

    //create database object
    Database db = DBHelper.CreateDatabase();

    //create SELECT sql statement to be executed using string builder
    //add WHERE 1 = 1 at the end

    //add where [optional] statements
    if (String.IsNullOrEmpty(pKeyword) == false)
    {
        //Create AND statement for ?Keyword
    }

    //add group by order by queries
    sql.Append(" GROUP BY c.city_id ");
    sql.Append(" ORDER BY city_name ");

    //Convert SQL String To DbCommand Object
    DbCommand comm = db.GetSqlStringCommand(sql.ToString());

    //Map Variables to mysql ?Parameters
    db.AddInParameter(comm, "?StateID", DbType.Int64, pStateID);
    db.AddInParameter(comm, "?Keyword", DbType.String, pKeyword);

    try
    {
        //execute sql statement and return results
    }

    catch (Exception ex)
    {
        throw ex;
    }
}

请在此处查看完整版本: http://friendpaste.com/6ZGJHRNxQ9J57ntFD5XZi1

下面是一个失败的尝试,我的朋友把它转换成MSSQL存储过程

ALTER PROCEDURE [dbo].[sp_Search]
@Keyword varchar(30)

AS

SELECT count(cc.course_id) as 'course_count',  c.*, con.* FROM city c
LEFT JOIN countries con on con.country_id = c.country_id
LEFT JOIN courses cc on cc.city_id = c.city_id
WHERE 1 = 1
AND CASE @Keyword WHEN (@Keyword <> NULL) THEN (AND c.state_name like @Keyword)  END
AND CASE @Keyword WHEN (pStateID > 0) THEN (AND c.state_id = @StateID AND c.country_id = 69 AND c.province_id = 0) END
AND CASE @Keyword WHEN (pProvinceID  > 0) THEN (AND c.province_id = @ProvinceID AND c.country_id = 52 AND c.state_id = 0) END

我还有一个事务DAL,我想把它转换成mysql存储过程