Gurus Place

MySQL: Dynamic Select Queries

June 3, 2007 on 11:36 am | In MySQL | 4 Comments

As we start to transfer our code to stored procedures, now we would have to start putting more logic in MySQL than before. In the past we would create an sql statement in our hosting language, and pass it by to mysql, put as we see the more improvement of performance and security in writing stored procedures, (also we employee people just experienced in MySQL to handle the sql Job) we now need a way to manipulate our sql statements with sql itself.
What I’m putting here is an example for a search procedure. imagine that you are looking for a certain profiles in the database, while specifing some data to be checked upon, and some data if missing won’t be included in our condition, lets say a user would check on gender and a range of age for our table like

CREATE TABLE Profiles (
id INT PRIMARY KEY,
gender CHAR(1),
age INT);

ok let’s create our search stored procedure

CREATE PROCEDURE SearchProfiles(
  IN pGender CHAR(1),
  IN pAgeStart INT,
  IN pAgeEnd INT
BEGIN
  SELECT id FROM Profiles
    WHERE ((pGender IS NULL OR pGender = '') OR gender = pGender)
      AND (pAgeStart IS NULL OR age >= pAgeStart)
      AND (pAgeEnd IS NULL OR age <= pAgeEnd);
END


So now if you Null any of the inputs, it’s part of the query is removed, for example a NULL Gender would make the condition (pGender IS NULL OR pGender = ”) evaluates to true and it’s the right side of the total gender condition, which would let the gender = pGender Not Evaluated.
I guess you can improve the code more, by evaluating your stop conditions before you get in the select statement, and only calling a variable to test.

Hope it was useful