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,
ok let’s create our search stored procedure
CREATE PROCEDURE SearchProfiles(
IN pGender CHAR(1),
IN pAgeStart INT,
IN pAgeEnd INT
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 🙂