3 replies to this topic

[ankimo]

Hi, all.

I'm in the process of creating a function called, split_tables. One issue I'm having is that when I set a value called @half_records by dividing @all_records in 2, the value ends up as a float.

But I need that value in a limit clause. How do I convert @half_records to an int?

DROP PROCEDURE if exists split_tables;


delimiter $$

CREATE PROCEDURE split_tables(in_table varchar(255), in_file varchar(255))


BEGIN

  declare all_records int default 0;

  declare half_records int default 0;


  set @my_query=concat('select count(*) into @all_records from ', in_table);

  prepare s1 from @my_query;

  execute s1;

  deallocate prepare s1;



  set @half_records = @all_records/2;

  set @my_query=concat('create table ', in_table, '1 select * from ', in_table, 'limit ', @half_records);

  prepare s2 from @my_query;

  execute s2;

  deallocate prepare s2;


END$$

delimiter ;

Thanks.

[WingedPanther]

Which database platform are you using?

[ankimo]

sorry about that. i'm using mysql.

[Orjan]

I'm not sure it works at the limit field in a simple query, but I'll try the round() function

select * from my_table limit round(23.45)