Gurus Place

MySQL: Dynamic Select Queries

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

NULL Is Not A Value

Before a few months, I was working on some mysterious issue regarding some SQL statements, it was something like this.
SELECT count(*) from some_table WHERE (condition_a);
the result was:

+----------+
| count(*) |
+----------+
|   11223  |
+----------+

alright Now:
SELECT count(*) from some_table WHERE (condition_a) AND (condition_b);

+----------+
| count(*) |
+----------+
|     0    |
+----------+
aha Ok then:
SELECT count(*) from some_table WHERE condition_a AND NOT (condition_b);
The result was also
Before a few months, I was working on some mysterious issue regarding some SQL statements, it was something like this.
SELECT count(*) from some_table WHERE (condition_a);
the result was:

+----------+
| count(*) |
+----------+
|   11223  |
+----------+

alright Now:
SELECT count(*) from some_table WHERE (condition_a) AND (condition_b);

+----------+
| count(*) |
+----------+
|     0    |
+----------+

HA !!!!
Ok for those 11223 records the condition_a was true, from the second statement I could tell that condition_b was false, so NOT condition_b should be true, but it was also false from the third statement.
So for Logic to play around condition_b, and NOT condition_b both are false (How can that be) all theories of logic are broken (what is going on?)
After breaking up the two conditions, I found that I was comparing one of the values in condition_b with NULL, so in BOTH cases the result would be FALSE (or actually UNKNOWN which is casted to NULL), I’ve always assumed that NULL would be casted to 0, but the fact that it’s not.
So this was just a thing that you might want to take care of when you write your SQL statements.
Hope it was Useful