Convert DAL function into Stored Procedures in mysql

Question

Hi I have class for my Data Access Layer that contains a function called GetCitiesByLocation and it looks like this

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;
    }
}

See full version here: http://friendpaste.com/6ZGJHRNxQ9J57ntFD5XZi1

I would like to convert this to MYSQL Stored Procedure

Here is a failed attempt of my friend to convert it to MSSQL Stored Proc

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

I have also transactional DAL which i want to convert to mysql stored proc

Answer 1

I'm not an expert on MySQL but it would seem that you're trying to execute a Stored Procedure the same way you would execute a Parameterized Query and that doesn't work.

I also would be very surprised if your MSSQL Proc compiled as I would expect that your proc would start with

  ALTER PROCEDURE [dbo].[sp_Search]
    @Keyword varchar(30),
    @StateID int = null,
    @ProvinceID int = null

Or in MySQL

CREATE PROCEDURE sp_Search(Keyword varhar(30),
StateID int,
ProviceId int)

You would most like have to change your DAL in the following ways

1. Your existing DAL calls GetSqlStringCommand. You need to change that to GetSqlStringCommand to a GetStoredProcCommand.
2. your parameter names would have to change
3. the text woud be sp_search instead of "Select count(cc.course_id) as 'course_count..."