MySQL Forums
Forum List  »  Stored Procedures

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
4875
October 18, 2009 09:22AM
1496
October 19, 2009 09:31AM
1315
October 21, 2009 07:20AM
1253
October 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.