0

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 ?

1

User-defined variables such as @whereclause and @query will retain their values between calls. I.e. the variables get a value from call #1 to the procedure and will then keep those values when you get to call #2. That is why you have to initialise the variables to make your procedure work.

However, instead of using user-defined variables, it is good practice to DECLARE variables inside the procedure:

DECLARE whereclause TEXT unsigned DEFAULT NULL;
DECLARE query TEXT unsigned DEFAULT NULL; 

(For these kinds of variables you don't prefix them with the '@' character.)

0

It is spelled DEALLOCATE PREPARE, not DROP PREPARE.

https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

  • DEALLOCATE PREPARE also not working , I tried both. – AnjumSKhan Aug 21 '17 at 4:05
0

If you set p_country_iso_code to null in your query your @whereclause is not set and this in turn will not define or execute any query. If you use the null parameter alone you should see that there is no result produced.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.