mysql - 尝试在 MySql 中创建存储过程但出现错误

Question

我正在尝试创建一个存储过程但是出现错误，任何人都可以告诉我代码中的错误

DELIMITER //
        CREATE PROCEDURE search_contact_list(IN keyword varchar(255), IN key_country int(10),IN key_status int(10),IN key_religion varchar(100),IN acc_manager int(5),IN acc_position int(10))
        BEGIN
            SELECT contact.*,company.company_name,status.status_name,user.first_name as user_first_name,user.last_name as user_last_name,country.country_name

            FROM tbl_contact_master as contact 
            LEFT JOIN tbl_status_master as status ON status.status_id=contact.status_id 
            LEFT JOIN tbl_user_master as user ON user.login_id=contact.contact_owner_id 
            LEFT JOIN tbl_company_master as company ON company.company_id=contact.company_id
            LEFT JOIN tbl_country_master as country ON country.country_id=contact.country
            WHERE 
            CASE WHEN keyword IS NOT NULL THEN contact.first_name LIKE CONCAT('%', keyword ,'%' ) 
                    OR contact.last_name LIKE CONCAT('%', keyword ,'%' ) END
            AND CASE WHEN key_country IS NOT NULL THEN contact.country = key_country ELSE NULL END;
        END //
    DELIMITER ;

Answer 1

你可以试试这个，伙计:

DELIMITER //
DROP PROCEDURE IF EXISTS new_pro //
CREATE PROCEDURE new_pro (
    IN f_name VARCHAR(100),
    IN l_name VARCHAR(100)
)
BEGIN
    CASE
        WHEN (f_name IS NOT NULL OR (f_name != '')) THEN
            SELECT * FROM tbl_contact_master WHERE first_name = f_name;
        WHEN (l_name IS NOT NULL OR (l_name != '')) THEN
            SELECT * FROM tbl_contact_master WHERE last_name = l_name;
        WHEN (
            (f_name IS NOT NULL OR (f_name != ''))
            AND (l_name IS NOT NULL OR (l_name != ''))
        ) THEN
            SELECT * FROM tbl_contact_master WHERE first_name = f_name AND last_name = l_name;  
    END CASE;
END //
DELIMITER ;