Trying to print prime numbers from 1 to 1000 separated by with &. The error is "ERROR 1406 (22001) at line 28: Data too long for column 'p' at row 1"

Question

delimiter $$
create procedure a()
begin
declare x int default 3;
declare n int default 1;
declare y int default 0;
declare p varchar(2000) default '2';
case 
when x<=1000 then 
    while x<=1000 do
        while n<=x do
            if x%n=0  then set y=y+1;
            else set y=y;
            end if;
            set n = n+1;
                if y=2 then set p = concat(p ,'&' ,x );
    else set n=n;
    end if;
        end while;
    set n=1;
    set y=0;
    set x=x+1;
    end while;
else set y=0;
end case;
select p ;
end $$
call a();

I tried all the way that I could to find the error. I found many online resources where new solution exists, but I would like to find out the mistake within my code such that I could learn from mistakes.

Answer 1

There are at least two problems.

First, and the direct cause of your error, in mysql + is a numeric operation. To do string concatenation, do:

set p = concat(p, '&', x);

Second, you need to set n to 1 each iteration through the while x loop. As is, it reaches 1001 for the first x value and the while n<=1000 loop is effectively skipped for later values.