Stored Procedures with variables (Create Table)

[mla]

Hello,

i have a problem, i had created a stored procedure to create a temporary table.

[code]
CREATE PROCEDURE `sp_test`(var1 char(10))
begin
declare tbl char(20);
declare krit char(13);
set tbl = concat('tmp_',var1);
set krit = var1;
create table tbl select * from TableA where columnA = krit ;
alter table tbl add index(columnX);
insert into tbl select * from TableA where columnA in(select columnB from tbl);
end
[/code]

This procedure runs nearly normal, on fist call sp_test('test') the procedure creates a table test instead of tmp_test (defined at tbl variable).

and wenn i call the sp_test once again with an other value  i get the message (table already exist).

I just start with sp on MySQL but i (or better google) found no helpfull solution for this problem.

MySQL Version: 5.0.15 -nt auf Windows 2003 Server

Many thanks in advance.

Markus

[printf]

You say your creating a [b]temporary[/b] table, but I don't see where you are doing that. ALSO SET makes the variable value assigned to it exist for the life of the connection. Only DECLARE is a localized variable that exists in the current procedure call. So you will get [b]table already exist[/b], if you your not using [b]CREATE [TEMPORARY] TABLE tbl[/b]


printf

[mla]

here i create the table: create table tbl select * from TableA where columnA = krit ;

Edit: temporary is, maybe, a wrong word. because for my application is it temporary. will delete it on later point with "drop table"

but i had solved the problem by an other way, it is not possible to create a table directly with a variable.

instead of directly use the variable in the create i have to use dynamic sql like

[code]
.....
set @table ="value";
@sql = concat("create table ",@table," select * from table2");
prepare statemant from @sql;
execute statement;
deallocate prepare statement;
.....
[/code]

so, for me the problem is solved, but many thanks for your reply.