IS there any way to pass variable while creating table in database?

I want to dynamically create table from my program with columns name and table name given by me through my program. so i want to pass my variable to the stored procedure but error occurs as i do like this

create procedure maketable
@name varchar(50),
@roll int
as 
begin
create table new  (@name, @roll)
end

error is as foll开发者_StackOverflow社区ow

Msg 102, Level 15, State 1, Procedure maketable, Line 7
Incorrect syntax near '@name'.

You're out of luck, MySQL stored procedures can not contain dynamic SQL, and the variable implementation can't be used how you're trying to use it.

Because you know all of the column information in your calling code, is there a specific reason you can't just issue the CREATE TABLE directly from your code? That would be the most straightforward way to get this done. (Mind you, having non-temporary tables spring into existence on demand is probably a bad, bad idea...)

Here's a start - this creates a table with one field so you should be able to finish it off:

DROP PROCEDURE IF EXISTS maketable;

delimiter //

CREATE PROCEDURE maketable (IN table_name varchar(50), IN field1Name varchar(50), IN field1Type varchar(50))
BEGIN
   SET @s = CONCAT('CREATE TABLE ', table_name, '(', field1Name, ' ', field1Type, ')');
   PREPARE stmt1 FROM @s;
   EXECUTE stmt1;
END//
delimiter ;

CALL maketable('tst', 'id', 'INT');