mla Posted November 23, 2006 Share Posted November 23, 2006 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 Link to comment https://forums.phpfreaks.com/topic/28246-stored-procedures-with-variables-create-table/ Share on other sites More sharing options...
printf Posted November 24, 2006 Share Posted November 24, 2006 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 Link to comment https://forums.phpfreaks.com/topic/28246-stored-procedures-with-variables-create-table/#findComment-129557 Share on other sites More sharing options...
mla Posted November 24, 2006 Author Share Posted November 24, 2006 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. Link to comment https://forums.phpfreaks.com/topic/28246-stored-procedures-with-variables-create-table/#findComment-129594 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.