create procedure -> create table

MySQL & MariaDB Other MySQL® Questions
1

hello,

im writing an procedure to create tables.

#v+

CREATE PROCEDURE aaa (IN a char(20))
CREATE TABLE a (id int);
Query OK, 0 rows affected (0.01 sec)
call aaa(“bigtable”);
Query OK, 0 rows affected (0.01 sec)

show tables;
±---------------+
| Tables_in_test |
±---------------+
| a |
±---------------+
1 rows in set (0.00 sec)
#v-

How can i write the procedure to take the value of a to create the table?

2

Lol “dynamic” SQL is the solution.

CREATE PROCEDURE aaa (IN a char(20))
BEGIN
SET @q1=‘CREATE TABLE ‘;
SET @q2=’ (id int)’;
SET @query=concat(@q1,a,@q2);
prepare Stat from @query;
execute Stat;
END;

3

Are you doing this just for fun or are you intending to use this in an application?

Because generally It’s not recommended that you create SQL statements dynamically like this in a stored procedure.
The reason is that you get very poor performance due to that the DBMS can’t cache the “compiled” version of the stored procedure since the SQL is dynamically created each time.

4

Hello sterin,

is their a better “Built-In-Solution”?
AFAIK are Stored Procedueres not very well implemented in MySQL anyway. So even when they cached, their are cached on a thread-basis. or?

Even Oracle uses dynamic SQL (“EXECUTE IMMEDIATE”) for tasks like mine. Maybe perfomance ist not that important. I would like to use them for regular administration-tasks. Best done with the scheduler upcomming im MySQL 6.0 )

thx

5

No better built in solution.

My question is more like why you want to use stored procedures for this at all.
Why not write this code in your application programming language instead. Much better string handling and much more complete language.

And I’m sure you have a good reason, but I’m curious why you have to create new tables so often that you feel that you should create a stored procedure for it?

When I write an application you design the database once and after that the application runs on that design until you upgrade it and at that point you have a SQL-script that you have tested a lot of times that performs the upgrade.

As you can understand I’m not that very fond of Stored Procedures in the first place, they have their uses when you for example want to avoid sending a lot of data over the link to the client, etc, and banks for example use them to place security in the DBMS instead of creating a well defined API that the application is using for accessing the data.

But my main reason why I avoid them is that I think that it gets harder to debug applications that use them. Since you can’t for example step through all code since some of the functionality is placed in the stored procedure and you can’t usually step through the code in the stored procedure in your normal debugger.

6

Oh no Ive got no good reason:-)

Im playing around and having some fun. But im planing to use it with the scheduler in 6.0.
But in the end nothing beates Perl/DBI )