Unable to call procedure with valid arguments in MySQL

Question

I have written a PL/SQL block which needs to perform the following task :

2 tables have been created :

1) Borrower :

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| rollno      | int(11)     | NO   | PRI | NULL    |       |
| name        | varchar(30) | YES  |     | NULL    |       |
| dataofissue | date        | YES  |     | NULL    |       |
| nameofbook  | varchar(20) | YES  |     | NULL    |       |
| status      | varchar(2)  | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

Its content is :

+--------+------+-------------+------------+--------+
| rollno | name | dataofissue | nameofbook | status |
+--------+------+-------------+------------+--------+
|      1 | a    | 2018-09-10  | Ba         | I      |
|      2 | b    | 2018-09-10  | Bb         | I      |
|      3 | c    | 2018-09-01  | Cc         | R      |
|      4 | d    | 2018-08-01  | Dd         | I      |
|      5 | e    | 2018-09-21  | Ee         | I      |
|      6 | f    | 2018-09-18  | Ff         | I      |
+--------+------+-------------+------------+--------+

2) Fine - It has no data. But, its schema is :

+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| rollno | int(11) | NO   | PRI | NULL    |       |
| days   | int(11) | NO   |     | NULL    |       |
| amt    | int(11) | NO   |     | NULL    |       |
+--------+---------+------+-----+---------+-------+

Check the no. of days(from date of issue to current date) in Borrower table :

• if days are between 15 to 30 then fine amounts will be $5/day.
• If no. of days>30 then fine amount will be $50/day and for days less than 30, $5/day.

Then, the status will change from I to R. Fine will only be calculated for those whose status is I and not R.

• I indicate book issued.
• R indicate book returned.

The relevant information will be stored in the Fine table with amt as the total fine imposed and date as the curdate() when the book is returned.

Here, is the procedure that I've written but I'm unable to call the procedure with valid arguments.

mysql> Delimiter //
mysql> Create  procedure proce(in roll int, in bname varchar(20))
    -> Begin
    -> Declare notfound int default 0;
    -> Declare rno int default 0;
    -> Declare name varchar(20);
    -> Declare doi date;
    -> Declare nob varchar(20);
    -> Declare stat varchar(20);
    -> Declare diff int default 0;
    -> Declare fine  int default 0;
    -> Declare cursor_name cursor for select rollno, name, dataofissue, nameofbook, status from borrower;
    -> Declare continue handler for NOT FOUND set notfound=1;
    -> Open cursor_name;
    -> loop_1 : LOOP
    -> fetch cursor_name into rno, name, doi, nob, stat ;
    -> If notfound=1 then
    -> leave loop_1;
    -> end if;
    -> 
    -> If(rno=roll and nob=bname) then
    -> 
    -> Select datediff(curdate(),doi) as DAYS into diff;
    -> if(stat=“I”) then
    -> if(diff >=15  and diff <=30 ) then
    -> set fine=( diff-15 )*5;
    -> elseif( diff > 30 ) then 
    -> set fine=(diff-30)*50 + 15*5;
    -> end if;
    -> insert into fine values(rno,diff,fine);
    -> update borrower set status='D' where rollno=rno; 
    -> end if;
    -> 
    -> 
    -> End if;
    -> 
    -> End LOOP loop_1;
    -> Close cursor_name;
    -> End;//
Query OK, 0 rows affected (0.03 sec)

I call it here:

mysql> call proce(1,"Ba")//

ERROR 1054 (42S22): Unknown column '“I”' in 'field list'

Answer 1

The odd quotes in this statement if(stat=“I”) seem to be the problem - change to single quote and the proc works.

drop table if exists borrower,fine; create table Borrower( rollno int(11) , name varchar(30), dataofissue date,
nameofbook varchar(20), status varchar(2)
); insert into borrower values ( 1 , 'a' , '2018-09-10' , 'Ba' , 'I'),
( 2 , 'b' , '2018-09-10' , 'Bb' , 'I'),
( 3 , 'c' , '2018-09-01' , 'Cc' , 'R'),
( 4 , 'd' , '2018-08-01' , 'Dd' , 'I'),
( 5 , 'e' , '2018-09-21' , 'Ee' , 'I'),
( 6 , 'f' , '2018-09-18' , 'Ff' , 'I');

create table fine
( rollno  int(11),
 days    int(11), 
 amt     int(11));

drop procedure if exists proce;
 Delimiter //
 Create  procedure proce(in roll int, in bname varchar(20))
     Begin
     Declare notfound int default 0;
     Declare rno int default 0;
     Declare name varchar(20);
     Declare doi date;
     Declare nob varchar(20);
     Declare stat varchar(20);
     Declare diff int default 0;
     Declare fine  int default 0;
     Declare cursor_name cursor for select rollno, name, dataofissue, nameofbook, status from borrower;
     Declare continue handler for NOT FOUND set notfound=1;
     Open cursor_name;
     loop_1 : LOOP
     fetch cursor_name into rno, name, doi, nob, stat ;
     If notfound=1 then
     leave loop_1;
     end if;

     If(rno=roll and nob=bname) then

     Select datediff(curdate(),doi) as DAYS into diff;

      if(stat='I') then
        if(diff >=15  and diff <=30 ) then
            set fine=( diff-15 )*5;
        elseif( diff > 30 ) then 
        set fine=(diff-30)*50 + 15*5;
        end if;
     insert into fine values(rno,diff,fine);
     update borrower set status='D' where rollno=rno; 
     end if;


     End if;


     End LOOP loop_1;
     Close cursor_name;
     End //

call proce(1,'Ba');

select * from fine;

+--------+------+------+
| rollno | days | amt  |
+--------+------+------+
|      1 |   42 |  675 |
+--------+------+------+
1 row in set (0.00 sec)

I would also check that columns,declared variables and parameters don't have the same name and backtick status.