CODEWITHSUNDEEP
mysqlsqlif-statement
Muhammad Usama Mashkoor
Muhammad Usama Mashkoor

Reputation: 847

if conditions are not working after where clause in Mysql stored procedure

I am creating a Mysql stored procedure. What I want to do here is to add multiple and conditions if parameters values are not empty. I have written a query inside the stored procedure but it is not working.

Here is the query

BEGIN
    SELECT 
        c.user_id, u.fname, u.lname, u.email, 
        c.name, c.raised_amount, c.goal_amount, c.created_at 
    FROM
        users u
    INNER JOIN 
        city c ON u.id = c.user_id 
    WHERE 
        c.user_id = user_id 

    IF( city_name !='', and c.name LIKE CONCAT('%',city_name, '%') , 1)

    IF( city_raised_max != '' AND city_raised_min != '', and c.raised_amount BETWEEN city_raised_min AND city_raised_max , '%'), 1);   

END

These three are the parameters

  1. user_id
  2. city_raised_min
  3. city_raised_max

What I want to here is that i want to add multiple and conditions if parameters are not empty.

but the query is giving me syntax error on this line you have an error in your sql syntax error check the manual that corresponds to your mysql server version for the right syntax near.

IF( city_name !='', and c.name LIKE CONCAT('%',city_name, '%') , 1)

Kindly help me on this issue i have trying to resolve this for 1 day but still no success.

Thanks and Regards

Upvotes: 0

Views: 1855

Answers (3)

Blank
Blank

Reputation: 12378

Use or to do the trick:

BEGIN

    Select c.user_id,u.fname,u.lname,u.email,c.name,c.raised_amount,c.goal_amount,c.created_at from users u
    INNER JOIN city c on u.id = c.user_id 

    WHERE c.user_id=user_id 

    AND (city_name = '' OR city_name IS NULL OR c.name LIKE CONCAT('%',city_name, '%'))

    AND (city_raised_max  = '' OR city_raised_max IS NULL OR city_raised_max >= c.raised_amount)

    AND (city_raised_min = '' OR city_raised_min IS NULL OR city_raised_min <= c.raised_amount)
END

Edit:

How does this query work?
Take (city_name = '' OR city_name IS NULL OR c.name LIKE CONCAT('%',city_name, '%')) as an example:

if city_name = '' is true, then expressions after first or will not be calculated;
if city_name = '' is false, then if city_name IS NULL is true, then expressions after second or will not be calculated;
if city_name = '' and city_name IS NULL are false, the last expression will be calculated.

besides, here you have three criterias, of cause we should use and.

Upvotes: 3

Psi
Psi

Reputation: 6783

Forward's Answer with correct brackets and corrected comparison to maintain the logic:

BEGIN

Select c.user_id,u.fname,u.lname,u.email,c.name,c.raised_amount,c.goal_amount,c.created_at from users u
INNER JOIN city c on u.id = c.user_id 

WHERE c.user_id=user_id 

AND (city_name = '' OR c.name LIKE CONCAT('%',city_name, '%'))

AND (city_raised_max = '' OR city_raised_min = '' OR c.raised_amount BETWEEN city_raised_min AND city_raised_max);

END

Upvotes: 1

Guillaume Sainthillier
Guillaume Sainthillier

Reputation: 1685

You have a typo in your query. Try to replace it with :

IF( city_name != '' and c.name LIKE CONCAT('%',city_name, '%'))

Upvotes: -1

Related Questions