CODEWITHSUNDEEP
mysql
lapots
lapots

Reputation: 13395

stored procedure sql syntax error

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

Upvotes: 0

Views: 95

Answers (1)

Bgi
Bgi

Reputation: 2494

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

Upvotes: 1

Related Questions