Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to create a parametrized MySQL table?

Posted on 2014-09-19
2
Medium Priority
?
220 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 36

Accepted Solution

by:
ste5an earned 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

578 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