CODEWITHSUNDEEP
phpmysqlsqlselectstored-procedures
Javad
Javad

Reputation: 63

how to use a condition in mysql stored procedure

I want to write a MySQL stored procedure and give 4 variables _keyword , _catid,_low and high then if _catid was not empty filter results where category_id column equals _catid and if it(_catid) was empty do nothing something like this:

CREATE DEFINER = `root`@`localhost` PROCEDURE `sp_company_search` (
    IN `_catid` INT,
    IN `_keyword` VARCHAR (200) CHARSET utf8,
    IN `_low` INT,
    IN `_high` INT
) NO SQL SELECT
    i.images_id,
    c. NAME,
    c.address,
    c.id,
    cat.title,
    c.extra
FROM
    company c
LEFT JOIN category cat ON c.category_id = cat.id
LEFT JOIN company_images i ON c.id = i.company_id
WHERE
    (
        c. NAME LIKE _keyword
        OR c.extra LIKE _keyword
    ) 

    -- what i need TO DO: 
    -- IF (_catid != '') THEN { put "and c.category_id=_catid" IN QUERY }

    ORDER BY
        c.id DESC
    LIMIT _low, _high

Upvotes: 1

Views: 43

Answers (1)

Mureinik
Mureinik

Reputation: 311853

You can emulate this behavior with the or logical operator:

SELECT
    i.images_id,
    c. NAME,
    c.address,
    c.id,
    cat.title,
    c.extra
FROM
    company c
LEFT JOIN category cat ON c.category_id = cat.id
LEFT JOIN company_images i ON c.id = i.company_id
WHERE
    (
        c. NAME LIKE _keyword
        OR c.extra LIKE _keyword
    ) AND
    (_catid = '' OR c.category_id=_catid)
    ORDER BY
        c.id DESC
    LIMIT _low, _high

Upvotes: 1

Related Questions