CODEWITHSUNDEEP
mysqlstored-proceduresprepared-statement
vvr02
vvr02

Reputation: 611

MySQL Prepared Statements Appeding Where Condition Not Working

Here is the actual query

select taken_date, DATE_FORMAT(taken_date, '%Y') taken_date_year, count(id) num_of_orders, sum(total_order_days) total_work_days from
(   
    select id, taken_date, getNumOfWorkDaysForOrder(order.order_num) total_order_days from order 
    where order.is_active = 1 and order.deleted_at is null and order.vendor_id = vendor_input  and 
        order.company_id = company_input and order.contact_id = contact_input and order.candidate_id = candidate_input
    order by taken_date 
) as order_years group by YEAR(taken_date) order by taken_date desc;

I want to add where condition based on the input if it is not null, tried prepared statements and concatenation to add the where condition to the query but no luck. 

DELIMITER $$
CREATE PROCEDURE 
  getAllActiveOrdersGroupByTakenDate(vendor_input INT, company_input INT, contact_input INT, candidate_input INT)
BEGIN
    SET @prepareQuery = "select id, taken_date, getNumOfWorkDaysForOrder(order.order_num) total_order_days from order 
        where order.vendor_id = "+ vendor_input +" and order.is_active = 1 and order.deleted_at is null";

    IF company_input IS NOT NULL THEN
        SET @prepareQuery = CONCAT(@prepareQuery, ' ', "and order.company_id = "+company_input);
    END IF;
    IF contact_input IS NOT NULL THEN
        SET @prepareQuery = CONCAT(@prepareQuery, ' ', "and order.contact_id = "+contact_input);
    END IF;
    IF candidate_input IS NOT NULL THEN
        SET @prepareQuery = CONCAT(@prepareQuery, ' ', "and order.candidate_id = "+candidate_input);
    END IF;

    SET @finalQueryPart1 = CONCAT("select taken_date, DATE_FORMAT(taken_date, '%Y') taken_date_year, count(id) num_of_orders, sum(total_order_days) total_work_days from
    (", @prepareQuery);

    SET @finalQuery = CONCAT(@finalQueryPart1, ") as order_years group by YEAR(taken_date) order by taken_date desc");

    PREPARE stmt FROM @finalQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END $$
DELIMITER ;

Can someone help me to achieve this?

Upvotes: 1

Views: 473

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562320

Update: you had an issue with your CONCAT() syntax before you edited your question.

When you want to append content, you must assign it back to the original string. CONCAT() is a function that returns the concatenated string. It does not have any side-effect of modifying the variable you use as an argument.

WRONG:

CONCAT(@prepareQuery, ' ', "and order.company_id=company_input");

RIGHT:

SET @prepareQuery = CONCAT(@prepareQuery, ' ', "and order.company_id=company_input");

Also, I'm not sure if you can reference the procedure input parameters in these expressions.

Frankly, I hardly ever use stored procedures. MySQL's implementation of stored procedures sucks. It's inefficient, doesn't save compiled procedures, there's no debugger, there are no packages, and so on.

Mostly I just execute dynamic SQL from my applications. There you have debugging, code reuse, familiar string manipulation in a familiar language.

I understand that stored procedures are the tradition in Oracle and Microsoft SQL Server communities, but it's really better to avoid stored procedures in MySQL.

Upvotes: 1

Related Questions