MySql Stored procedure WHERE "variabilised" according to a parameter

Question

I'm totally new with stored procedure and I'm trying to understand its basic concepts. This is my first one and of course there is something wrong.

Basically the query is going to be the same (the original is more complex and there are other operations) but the WHERE clause changes according to the selType param. So what I'm trying to do is a sort of "variabilisation" of the WHERE clause according to the param value. I don't know whether this is the correct approach and, if yes, what's wrong with it.

DELIMITER //

CREATE PROCEDURE `testProcedure` (IN addressId INT, IN selType BOOLEAN)
BEGIN
DECLARE whereUserCriteria VARCHAR(127);
IF selType = 1 THEN
    SET whereUserCriteria = CONCAT('address_id = ', addressId);
ELSE
    SET whereUserCriteria = 'address_id = 1';
END IF;

SELECT whatever
FROM wherever AS ad
WHERE whereUserCriteria ;

END //

It's nice to see that when it's not variabilised, it works perfectly but, as soon as i use a variable to make it dynamic, it stops working. Of course this is a mere example aimed to understand what's the best approach in cases like this.

If you want create a dynamic query check [**this**](http://stackoverflow.com/a/5728155/3470178) — Juan Carlos Oropeza, Feb 08 '17 at 13:51

score 2 · Accepted Answer · answered Feb 08 '17 at 14:07

You can prepare query concatenating the queries and condition together and execute that using Prepared Execute statement as follows(as mentioned in the comment above):

DELIMITER //

CREATE PROCEDURE `testProcedure` (IN addressId INT, IN selType BOOLEAN)
BEGIN
DECLARE whereUserCriteria VARCHAR(127);
IF selType = 1 THEN
    SET whereUserCriteria = CONCAT('address_id = ', addressId);
ELSE
    SET whereUserCriteria = 'address_id = 1';
END IF;

SET @myQuery = '';
SET @myQuery = CONCAT("SELECT whatever FROM wherever AS ad
WHERE ",whereUserCriteria,") ;
PREPARE stmQuery FROM @myQuery;
EXECUTE stmQuery;
DEALLOCATE PREPARE stmQuery;

END //

DELIMITER ;

score 0 · Answer 2 · answered Feb 08 '17 at 13:53

You probably want do dynamic query.

But you can rewrite your sample using CASE like this (but not sure if that is what you want):

SELECT whatever
FROM wherever AS ad
WHERE address_id = CASE WHEN selType = 1 
                        THEN addressId
                        ELSE 1
                   END;

MySql Stored procedure WHERE "variabilised" according to a parameter

2 Answers2