CODEWITHSUNDEEP
mysqlstored-procedureswhere-clause
Roberto Rizzi
Roberto Rizzi

Reputation: 1543

MySql Stored procedure WHERE "variabilised" according to a parameter

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.

Upvotes: 0

Views: 61

Answers (2)

krishna aryal
krishna aryal

Reputation: 499

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 ;

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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;

Upvotes: 0

Related Questions