create table via stored procedure (passing the table name to it)

hi there, I am trying to create a stored procedure that i can pass the table name to and it will create a table with that name. but for some reason it creates with what i have defined as the variable name . In the case of the example below it creates a table called 'tname'

for example

CREATE PROCEDURE createtable(tname varchar(20))
BEGIN
DROP TABLE IF EXISTS tname;
CREATE TABLE tname SELECT * FROM anothertable WHERE value = 'something';
END // 

now i call it passing my desired table name

mysql> call createtable(accounts); 

It works fine apart from the fact it ignores my desired table name of 'accounts' and instead creates a table called 'tname'

Is there something basic i'm doing wrong here?

any help would be greatly appreciated
Cheers

---------- Post updated at 06:13 AM ---------- Previous update was at 04:52 AM ----------

oops, i think ive posted this question in the wrong area, is there any chance a mod could move it to the correct area?

Moved by request.

Also, I'm not 100% sure, but I think you have to define the tname parameter explicitly, eg:

CREATE PROCEDURE createtable(IN tname varchar(20))
BEGIN
DROP TABLE IF EXISTS tname;
CREATE TABLE tname SELECT * FROM anothertable WHERE value = 'something';
END //

thanks pludi, Defining ' IN' doesn't do anything unfortunately as IN is the default direction if no direction is defined.

I tried it though and as expected it still creates a table called 'tname'

OK, I've read up on Stored Procedures. Seems you can only use DDL statements if you CONCAT them into a variable, and then use that as a prepared statement.

CREATE PROCEDURE createtable(IN tname varchar(20))
BEGIN

SET @s = CONCAT('DROP TABLE IF EXISTS ', tname);
PREPARE stm FROM @s;
EXECUTE stm;

SET @s = CONCAT('CREATE TABLE ', tname, ' SELECT * FROM anothertable WHERE value = '''something''');
PREPARE stm FROM @s;
EXECUTE stm;

END //

thanks Pludi, there definitely is lot more to it than i thought there was..unfortunately it thinks the table i want to create is field !?!.

Ive dropped the WHERE condition to simplify, im creating the table with data from the already existing 'admin' table

mysql> DELIMITER //
mysql> CREATE PROCEDURE createtable(IN tname varchar(20))
    -> BEGIN
    -> 
    -> SET @s = CONCAT('DROP TABLE IF EXISTS ', tname);
    -> PREPARE stm FROM @s;
    -> EXECUTE stm;
    -> 
    -> SET @s = CONCAT('CREATE TABLE ', tname, ' SELECT * FROM admin');
    -> PREPARE stm FROM @s;
    -> EXECUTE stm;
    -> 
    -> END //
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER ;
mysql> call createtable(accounts);
ERROR 1054 (42S22): Unknown column 'accounts' in 'field list'
mysql>

Its definitely getting there though, thank you again for your help

I've tried it. If you want to pass in a VARCHAR, you'll have to quote it, eg

CALL createtable("accounts");

thanks Pludi, after reading the link you posted up earlier, i figured out i needed to use quotes , but thank you anyway it all works perfectly, ive added some kudos via the "thanks" button

cheers