stored procedures - MySQL Prepared Statements Appeding Where Condition Not Working -

here 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 (        select id, taken_date, getnumofworkdaysfororder(order.order_num) total_order_days order      order.is_active = 1 , order.deleted_at null , order.vendor_id = vendor_input  ,          order.company_id = company_input , order.contact_id = contact_input , order.candidate_id = candidate_input     order taken_date  ) order_years group year(taken_date) order taken_date desc; 

i want add condition based on input if not null, tried prepared statements , concatenation add condition query 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 order          order.vendor_id = "+ vendor_input +" , order.is_active = 1 , order.deleted_at null";      if company_input not null         set @preparequery = concat(@preparequery, ' ', "and order.company_id = "+company_input);     end if;     if contact_input not null         set @preparequery = concat(@preparequery, ' ', "and order.contact_id = "+contact_input);     end if;     if candidate_input not null         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     (", @preparequery);      set @finalquery = concat(@finalquerypart1, ") order_years group year(taken_date) order taken_date desc");      prepare stmt @finalquery;     execute stmt;     deallocate prepare stmt;  end $$ delimiter ; 

can me achieve this?

update: had issue concat() syntax before edited question.

when want append content, must assign original string. concat() function returns concatenated string. not have side-effect of modifying variable use 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 can reference procedure input parameters in these expressions.

frankly, 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 no packages, , on.

mostly execute dynamic sql applications. there have debugging, code reuse, familiar string manipulation in familiar language.

i understand stored procedures tradition in oracle , microsoft sql server communities, it's better avoid stored procedures in mysql.