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; " +
MySqlCommand command = new MySqlCommand(strCmd, connection);

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;
MySqlDataReader reader = command.ExecuteReader();
table = new DataTable();
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 4793 Björn Olsson 10/18/2009 09:22AM
Re: variable where clause 1491 Chad Bourque 10/19/2009 09:31AM
Re: variable where clause 1310 Björn Olsson 10/21/2009 07:20AM
Re: variable where clause 1250 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.