Why can't get right logical query for the if statement in mysql procedure?

Question

Query all data when year is 18:

SELECT * from tb where year=18 //
+----+------+------+------+
| id | name | year | num  |
+----+------+------+------+
|  2 | a    |   18 |  400 |
|  4 | b    |   18 |  200 |
|  6 | c    |   18 |  100 |
+----+------+------+------+

Now i wrote a mysql procedure:

create procedure show_data(in type char,myear int)
begin
if type = "all" then 
    SELECT * from tb where year=myear;
elseif type != "all" then
    SELECT * from tb where name=type and year=myear;
end if;
end //

The logic in procedure show_data is clear:when the input argument type is all ,and myear is 18,the query is just SELECT * from tb where year=18 according to the procedure.

What i got with call show_data("all",18) is as below:

call show_data("all",18)//
+----+------+------+------+
| id | name | year | num  |
+----+------+------+------+
|  2 | a    |   18 |  400 |
+----+------+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

show warnings//
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'type' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

give a IN value for char like `char(3)`. – James Oct 17 '19 at 10:02 — James, Oct 17 '19 at 10:02

GMB · Accepted Answer · 2019-10-17T10:08:36.890

You are declaring variable type as char. This only allows a single character. Hence the error that you are getting when you try to assign a string that contains three characters ('all').

Consider this example:

delimiter //
create procedure debug_char(in type char, myear int)
begin
    select type;
end 
//

call debug_char('abc', 1);

Yields:

Error: ER_DATA_TOO_LONG: Data too long for column 'type' at row 1

You would need to change the datatype to char(3) so the value can fit in it (you actually want the same max char length as in column name if that is more than 3).

Note: your code could be simplified by moving the logic to the query itself instead of using if, as follows:

delimiter //
create procedure debug_char(in type char(3), myear int)
begin
    select * from tb where (name = type or type = 'all') and year = myear;
end
//

Why can't get right logical query for the if statement in mysql procedure?

1 Answers1