stored procedure sql syntax error

Question

I've got 5 tables in database

ApartamentClass(idclass,descript)
Room(idroom,idclass,beds,isfree)
ItemPrice(iditem,idclass,description,price)
Client(idclient,fio)
Active(idcontract,idclient,idroom,days,price,idclass)

I need to create a stored procedure that check whether exists free room with certain class(param1) and number of beds (param2) and create renting contract for this room and client(fio)(param3) for several days (param4). The price depends on class of the room(higher class -> higher price for one bed and one day).

create procedure UserRequest(
    in param1 int,
    in param2 int,
    in param3 varchar(100),
    in param4 int
)
begin   
    declare iroom int default 0;
    select idroom into iroom from room
    where beds = param2 and idclass = param1 and isfree = 0
    limit 1;


    if not (iroom=0) then
        update room set isfree = 1
        where idroom = iroom;
    end if;


    declare iclient int default 0;
    select idclient into iclient from client
    where fio = param3
    limit 1;


    declare bedprice decimal default 0.0;
    select (param2 * price) into bedprice from itemprice
    where description = "bed" and idclass = param1;

    declare dayprice decimal default 0.0;
    select (param4 * price) into dayprice from itemprice
    where description = "day" and idclass = param1;

    declare price decimal default 0.0;
    set price = bedprice + dayprice;


    insert into active(idclient,idroom,days,price,idclass)
    values(iclient,iroom,param4,price,param1);
end

But I always get SQL syntax error. I can't get where the problem is. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare iclient int default 0; select idclient into iclient from client wh' at line 20

Bgi · Accepted Answer · 2013-06-05 11:14:46Z

1

All the DECLARE statements must be at the beginning of the BEGIN... END block as stated in the MySQL documentation here: http://dev.mysql.com/doc/refman/5.0/en/declare.html

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

So, you might want to try the following code:

create procedure UserRequest(
    in param1 int,
    in param2 int,
    in param3 varchar(100),
    in param4 int
)
begin   
    declare iroom int default 0;
    declare iclient int default 0;
    declare bedprice decimal default 0.0;
    declare dayprice decimal default 0.0;
    declare price decimal default 0.0;

    select idroom into iroom from room
    where beds = param2 and idclass = param1 and isfree = 0
    limit 1;


    if not (iroom=0) then
        update room set isfree = 1
        where idroom = iroom;
    end if;


    select idclient into iclient from client
    where fio = param3
    limit 1;

    select (param2 * price) into bedprice from itemprice
    where description = "bed" and idclass = param1;

    select (param4 * price) into dayprice from itemprice
    where description = "day" and idclass = param1;

    set price = bedprice + dayprice;


    insert into active(idclient,idroom,days,price,idclass)
    values(iclient,iroom,param4,price,param1);
end

answered Jun 5, 2013 at 11:14

Bgi

2,50414 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lapots Over a year ago

Yes it works! But why position of declare does have matter in MySQL?

Collectives™ on Stack Overflow

stored procedure sql syntax error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related