CODEWITHSUNDEEP
mysqlstored-procedures
Vivek
Vivek

Reputation: 43

Trying to create a stored procedure in MySql but getting an error

i'am trying to create a Stored Procedure But Getting an error,can anyone tell what is Wrong in my code 

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 ;

Upvotes: 1

Views: 40

Answers (3)

Barmar
Barmar

Reputation: 780798

The following should work:

DELIMITER //
CREATE PROCEDURE new_pro
(IN f_name VARCHAR(100),IN l_name VARCHAR(100))
IF f_name != '' AND l_name != '' THEN
     SELECT * FROM tbl_contact_master
     WHERE first_name = f_name AND last_name = l_name;
ELSEIF f_name != '' THEN
     SELECT * FROM tbl_contact_master
     WHERE first_name = f_name;
ELSEIF l_name != '' THEN
     SELECT * FROM tbl_contact_master
     WHERE last_name = l_name;
END IF
//
DELIMITER ;

You were missing the THEN keywords in your IF statements. And the SELECT statements in the body of IF need to end with ;. You need to use a DELIMITER statement first so that those inner ; don't end the procedure.

You don't need BEGIN and END because IF is a single statement.

And the test for both f_name and l_name being filled in needs to be first. Otherwise, it will just do the f_name query.

Upvotes: 0

Zymon Castaneda
Zymon Castaneda

Reputation: 759

You can try this, mate: 

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 ;

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

First and end to add delimiter. like this

DELIMITER //
    CREATE PROCEDURE new_pro
    (IN f_name VARCHAR(100),IN l_name VARCHAR(100))
    BEGIN
    IF (f_name != '')
     BEGIN
         SELECT * FROM tbl_contact_master
         WHERE first_name = f_name
     END
    ELSE IF (l_name != '')
     BEGIN
         SELECT * FROM tbl_contact_master
         WHERE last_name = l_name
     END
    ELSE IF (f_name != '' && l_name != '')
       BEGIN
         SELECT * FROM tbl_contact_master
         WHERE first_name = f_name AND last_name = l_name
       END
    END
    DELIMITER ;

Upvotes: 0

Related Questions