I am completely new to MySql and trying to use
PREPARE STATEMENT, but for unknown reasons previous working query is getting cached.
SP Code :
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_filter`( p_membership_status varchar(10), p_country_iso_code varchar(2), p_subdivision_1_iso_code char(5), p_districtname varchar(100), p_mobile_req tinyint, p_most_ordered_items_req tinyint, p_emailid_req tinyint, p_foreign_members_req tinyint ) BEGIN /* if we dont set below variables to null, problems persists */ set @whereclause = null; set @query = null; if(p_mobile_req = 1) then begin set @selectcolumn = 'mobile'; if(p_country_iso_code is not null) then begin set @whereclause = concat('country_iso_code = ', "'" , p_country_iso_code , "'"); end; end if; end; end if; if(@whereclause is not null) then begin set @query = concat('select ', @selectcolumn, ' from member where ', @whereclause); PREPARE stmt1 FROM @query; EXECUTE stmt1; DROP PREPARE stmt1; end; end if; END
For example, if I call above sp like
call sp_filter(null, 'in', null, null, 1, 0, 0, 0); call sp_filter(null, null, null, null, 1, 0, 0, 0); // result of this 2nd call will present result of 1st call as if second parameter is now 'in' instead of null
I tried countless times, but of no use. So, I set both variables to null at the start and problem was solved. No idea why ?
What could be the reason behind this behavior ?