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

Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
# 1  
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


Code:
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


Code:
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?
# 2  
Moved by request.

Also, I'm not 100% sure, but I think you have to define the tname parameter explicitly, eg:
Code:
CREATE PROCEDURE createtable(IN tname varchar(20))
BEGIN
DROP TABLE IF EXISTS tname;
CREATE TABLE tname SELECT * FROM anothertable WHERE value = 'something';
END //

This User Gave Thanks to pludi For This Post:
# 3  
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' Smilie
# 4  
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.
Code:
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 //

This User Gave Thanks to pludi For This Post:
# 5  
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

Code:
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
# 6  
I've tried it. If you want to pass in a VARCHAR, you'll have to quote it, eg
Code:
CALL createtable("accounts");

This User Gave Thanks to pludi For This Post:
# 7  
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 Smilie
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Science: Computers
Difficulty: Easy
Ada Lovelace is often considered the first computer programmer.
True or False?

10 More Discussions You Might Find Interesting

1. Web Development

Getting Rid of Annoying Bootstrap Table Borders and Wayward Table Lines

Bootstrap is great; but we have had some issues with Bootstrapped <tables> (and legacy <fieldset> elements) showing annoying, wayward lines. I solved that problem today with this simple jQuery in the footer: <script> $(function(){ $('tr, td, fieldset,... (0 Replies)
Discussion started by: Neo
0 Replies

2. Shell Programming and Scripting

Build a table from a list by comparing existing table entries

I am new to this shell scripting.... I have a file which contains list of users. This files get updated when new user comes into the system. I want to create script which will give a table containing unique list of users. When I say unique, it means script should match table while parsing... (3 Replies)
Discussion started by: dchavan1901
3 Replies

3. Shell Programming and Scripting

Unix path stored as a column in table

The db2 table stores the unix path as below PARM VALUE RootPath $SRootDir Target $SRootDir/target $SRootDir is set in the env variable as /home/test/root In the shell script i read the table value and store it in a variable pth=db2 -x "select VALUE from... (2 Replies)
Discussion started by: 2jnags
2 Replies

4. UNIX for Dummies Questions & Answers

Creating a condensed table from a pre-existing table in putty

Hello, I'm working with putty on Windows 7 professional and I'd like to know if there's a way to gather specific lines from a pre-existing table and make a new table with that information. More specifically, I'd like the program to look at a specific column, say column N, and see if any of the... (5 Replies)
Discussion started by: Deedee393
5 Replies

5. Shell Programming and Scripting

How to create and call mysql stored procedure in perl?

Hi, I want to create MySQL stored procedure and call the stored procedure using perl. I tried like this: use DBI; my $dbh = DBI->connect ("DBI:mysql:test", "root", "ibab", { RaiseError => 1, PrintError => 0}); $create_procedure =... (5 Replies)
Discussion started by: vanitham
5 Replies

6. Shell Programming and Scripting

Read Table,View,Package,Function and Procedure Name in a File

Hi I am new to Unix shell scripting. But i need help to slove the below issue. Issue description: I want to read table, view names and package names in a file my plan to find the table name is : search "From" key word find the table or view To find the packge name : Search "Package... (5 Replies)
Discussion started by: sboss
5 Replies

7. Shell Programming and Scripting

Passing a value to stored procedure from unix shell script

Hi Dudes :) I want a unix shell script to pass value to SQL stored procedure. Below is the procedure declare res varchar2(10); begin odm_load_check('PRE_SANITY',res); dbms_output.put_line(res); end; select * from error_log; truncate table error_log; select * from test; (1 Reply)
Discussion started by: shirdi
1 Replies

8. Shell Programming and Scripting

select values from db1 table and insert into table of DB2

Hi I am having three oracle databases running in three different machine. their ip address is different. from one of the DB am able to access both the databases.(means am able to select values and insert values in to tables individually.) I need to fetch some data from DB1 table(say DB1 ip is... (2 Replies)
Discussion started by: aemunathan
2 Replies

9. Shell Programming and Scripting

loading the data to the partitioned table using procedure

Hi one help, I need one procedure to load data from flat file to table. Table name as input parameter for the procedure. can anyone help me Thanks, Raj, (1 Reply)
Discussion started by: easterraj
1 Replies

10. Shell Programming and Scripting

passing parameter 4m shell script to a DB stored procedure

hi all please tell me how to pass parameters 4m shell script to a DataBase stored procedure. To be specific i have sybase DB. i mean i want the syntax of the command.. how to connect to DB, pass user id and password, pass the required parameter to SP.. .. need ur help frnds.. hema (0 Replies)
Discussion started by: hema2026
0 Replies

Featured Tech Videos