Solved

How to create a parametrized MySQL table?

Posted on 2014-09-19
2
217 Views
Last Modified: 2014-09-19
Hi Expets!

Do you know a way to create a table with a name received as a parameter, inside a stored procedure?

by example:

CALL sp_pesquisa_lancamentos ("20140901","20140919",4,"the_name_I_want_for_the_table_to_be_created");

PROCEDURE sp_pesquisa_lancamentos (IN dt_ini date, IN dt_fim date, IN s_emp integer(11), IN  tabela varchar(20))
BEGIN
.....

 DROP TABLE IF EXISTS [tabela];
 CREATE TABLE  [tabela](
    Id int(6) NULL,
    CR varchar(50) NULL,
    DSP varchar(50) NULL,
    CT varchar(50) NULL,
    DCN varchar(10) NULL,
    DIA varchar(12) NULL,
    DT_LAN varchar(12) NULL,
    VALOR varchar(15) NULL,
    QTD varchar(5) NULL,
    DESTINO varchar(100) NULL,
    SALDO_C varchar(15) NULL,
    EMP varchar(10) NULL,
    ORIGEM  varchar(10) NULL
  );

....

Open in new window


Thanks in advance!
0
Comment
Question by:Eduardo Fuerte
2 Comments
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40332585
You need dynamic SQL with PREPARE and EXECUTE. Concat the table name into the prepare statement. A table name as parameter is not supported.

E.g.

PROCEDURE sp_pesquisa_lancamentos (
	IN dt_ini DATE, 
	IN dt_fim DATE, 
	IN s_emp INTEGER(11), 
	IN  tabela VARCHAR(20)
	)
BEGIN
	--[..]

	SET @Sql = CONCAT( "DROP TABLE IF EXISTS ", tabela, ";");
	PREPARE preparedStmt FROM @Sql;
	EXECUTE preparedStmt;
	DEALLOCATE PREPARE preparedStmt;

	--[..]
END;

Open in new window


Caveat: Cause you need string concatenation, this is prone to SQL injection. Consider a different approach, when posssible. Look for multitenant  solutions. E.g. use a discriminator in one big table instead of separate tables.
0
 

Author Closing Comment

by:Eduardo Fuerte
ID: 40333667
Fantastic!

By using this approach I solved all the issues

Drop table
create table
inserts....

Thank you very much!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question