1

Just can't understand why it's throwing this error?

Error Code: 1064. 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 'NULL' at line 1

drop procedure if exists dividir_tabla_por_usuario;

delimiter //
create procedure dividir_tabla_por_usuario(tabla varchar(100), columna_usuario varchar(100), columna_fecha varchar(100))
begin
  set @i = 0;
  set @q1 = concat('select count(*) from (select distinct ', columna_usuario ,' from ', tabla , ' order by ', columna_usuario ,' asc) as t1 into @c');
  prepare p1 from @q;
  execute p1;
while @i <= @c do
  set @r = 0;
  set @q2 = concat('select * from ', tabla , ' where ', columna_usuario ,' = (select ', columna_usuario ,' from (select @r:=@r +1 as ord, ', columna_usuario ,' from (select distinct ', columna_usuario ,' from ', tabla , 'order by ', columna_usuario ,' asc) as t2) as t3 where ord = ', @i ,' ) order by ', columna_fecha ,' asc limit 30000'); 
  prepare p2 from @q2;
  execute p2;
  set @i := @i + 1;
end while;
end//
delimiter ;

call dividir_tabla_por_usuario('mytable', 'mycolumn_1', 'mycolumn_2');

closed as off-topic by RolandoMySQLDBA, Mark Storey-Smith, Michael - sqlbot, Kin Shah, Marian Apr 25 '14 at 8:08

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – RolandoMySQLDBA, Mark Storey-Smith, Michael - sqlbot, Kin Shah, Marian
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 4
    You need dynamic SQL for this. You can't pass a table name as a variable – a_horse_with_no_name Feb 18 '14 at 13:50
  • Inside block (between Begin and End) you must have all declare statments before all other instruction. – user34551 Feb 20 '14 at 8:19
1

Typos, always typos.

Here is the final working code:

drop procedure if exists dividir_tabla_por_usuario;

delimiter //
create procedure dividir_tabla_por_usuario(tabla varchar(100), columna_usuario varchar(100), columna_fecha varchar(100))
begin
  set @i = 0;
  set @q1 = concat('select count(*) from (select distinct ', columna_usuario ,' from ', tabla , ' order by ', columna_usuario ,' asc) as t1 into @c');
  prepare p1 from @q1;
  execute p1;
while @i <= @c do
  set @r = 0;
  set @q2 = concat('(select distinct ', columna_usuario ,' from ', tabla , ' order by ', columna_usuario ,' asc) as t2)');
  set @q3 = concat('(select @r:=@r +1 as ord, ', columna_usuario ,' from ',@q2, ' as t3 where ord = ', @i ,' )');
  set @q4 = concat('(select ', columna_usuario ,' from ', @q3);
  set @q5 = concat('select * from ', tabla , ' where ', columna_usuario ,' = ', @q4 ,' order by ', columna_fecha ,' asc limit 30000'); 
  prepare p2 from @q5;
  execute p2;
  set @i := @i + 1;
end while;
end//
delimiter ;

call dividir_tabla_por_usuario('mytable', 'mycolumn1', 'mycolumn2');

Not the answer you're looking for? Browse other questions tagged or ask your own question.