Skip navigation links

MySQL Forums :: Stored Procedures :: variable where clause


Advanced Search

variable where clause
Posted by: Björn Olsson ()
Date: October 18, 2009 09:22AM

Is it possible to write stored procedures where you pass the where clause as a parameter, because I would like to sometimes only compare one field other times several fields. The following always gives 0 in result whatever I have in my where clause:

string strCmd = "CREATE PROCEDURE MyProcedure (query VarChar(255)) " +
"BEGIN " +
"SELECT IFNULL(Count(id),0) " +
"FROM MyTable " +
"WHERE query; " +
"END";
MySqlCommand command = new MySqlCommand(strCmd, connection);
command.ExecuteNonQuery();

I call my sp from the method

public static DataTable GetCount(string query)
{
MySqlCommand comm = new MySqlCommand();
comm.Connection = connection;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "MyProcedure";
comm.Parameters.Add("@query", MySqlDbType.VarChar).Value = query;
command.Connection.Open();
MySqlDataReader reader = command.ExecuteReader();
table = new DataTable();
table.Load(reader);
reader.Close();
return table;
}

And my input string 'query' can be like

FirstName = 'Bill'

or longer when needed like

FirstName = 'Bill' AND LastName = 'Smith' AND City = 'Los Angeles'

Options: ReplyQuote


Subject Views Written By Posted
variable where clause 3766 Björn Olsson 10/18/2009 09:22AM
Re: variable where clause 1361 Chad Bourque 10/19/2009 09:31AM
Re: variable where clause 1214 Björn Olsson 10/21/2009 07:20AM
Re: variable where clause 1154 Chad Bourque 10/21/2009 07:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.