mysql procedure is won't let me declare variable

Question

My goal for this procedure is to use the day of the week to determine the the adjusted price for a service. For instance, on the weekends, the price is increased by 10%. On Monday and Friday, the price is the same. And Tues through Thurs is decreased by 5%. The final message should be the new adjusted price.

I thought I was doing this correctly. But I am getting major errors with the declare values.

Any help will be appreciated. Thank you.

Create procedure DayOfWeek( inout p_price decimal(7,2), inout p_date date, out v_msg varchar(10))
begin
    declare increase10 double;
    set increase10 = p_price * (1.1);

    declare decrease5 double;   
    set decrease5 = p_price * (0.95);

    declare increase10cast varchar(10); 
    set increase10cast := cast(increase10 as char);

    declare decrease5cast varchar(10); 
    set decrease5cast := cast(decrease10 as char);

    declare regular varchar(10);
    set regular := cast(p_price as char );

    case 
    when p_price is null then
        set v_msg := 'null';
    when p_date is null then
        set v_msg := 'null';
    when weekday(p_date) in (0, 6) then
        set v_msg := increase10cast;
    when weekday(p_date) in (1, 5) then
        set v_msg := regular;   
    when weekday(p_date) in (2, 2, 4) then
        set v_msg := decrease5cast;
    end case;   

end;
#

Can you show your error messages? – endyourif Sep 23 '12 at 01:15 — endyourif, Sep 23 '12 at 01:15

Typist · Answer 1 · 2012-09-19T05:52:02.737

Declare all your variable first then go for set statement -

Create procedure a05_AdjustPrice_ByDayOfWeek( in p_price decimal(7,2), in p_date date, out v_msg varchar(10))
begin
    declare increase10 double;
    declare decrease5 double;   
    declare increase10cast varchar(10); 
    declare decrease5cast varchar(10); 
    declare regular varchar(10);

    set increase10 = p_price * (1.1);    
    set decrease5 = p_price * (0.95);    
    set increase10cast := cast(increase10 as char);    
    set decrease5cast := cast(decrease5 as char);    
    set regular := cast(p_price as char );

    case 
    when p_price is null then
        set v_msg := 'null';
    when p_date is null then
        set v_msg := 'null';
    when weekday(p_date) in (0, 6) then
        set v_msg := increase10cast;
    when weekday(p_date) in (1, 5) then
        set v_msg := regular;   
    when weekday(p_date) in (2, 2, 4) then
        set v_msg := decrease5cast;
    end case;   

end;

Declare statements must be at the start of Begin...End block. Refer Docs

Change INOUT param to IN param. You can execute it like -

call a05_AdjustPrice_ByDayOfWeek(100, '2012-09-09', @msg);
select @msg;

Thank you! If you think you can help again, why do I not get an output for v_msg? if I try something like call a05_AdjustPrice_ByDayOfWeek(100, '2012-09-09', @msg)# — user1682055, Sep 19 '12 at 05:03

mysql procedure is won't let me declare variable

1 Answers1