create table procedure

i need a mysql procedure that creates a table with 25 rows first row is date other 24 is hours like 0 1 2 3 ... I will send the procedure only 1 value it is table name and it will also will check if table already exist and will return a spesific error code or succes info    thanks ...
3XLcomAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
I see you are using the code I gave you here in some of your other questions, so please advise what else you are needing here.

Thanks!

Kevin
0
 
Kevin CrossChief Technology OfficerCommented:
3XLcom,

Do you really mean "rows" above OR are you looking to create a column called `date` and then columns `0`, `1`, `2`, ... `23`?  Rows would be creating a table with one column called `date` and then store 24 rows of :
'2010-09-21 00:00'
'2010-09-21 01:00'
...
'2010-09-21 23:00'

Just want to ensure we are on the same page.

If I am correct, you simply need to build a dynamic SQL string using the table name variable you pass and execute that dynamic SQL.

Kevin
0
 
Kevin CrossChief Technology OfficerCommented:
I use something similar in my article here, so you can read section "Dynamic" for an example:
http://www.experts-exchange.com/Database/MySQL/A_3638-A-MySQL-Tidbit-Dynamic-Export-To-XML-Stored-Procedure.html

Otherwise, check the manual page:
http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
3XLcomAuthor Commented:
no there will be a matris like this
every they we record 24 value so i want to store one line for each day and we have 650 different recorder for each day so i want to create a new table for each recorder.

so it will be sth. like this one row for date 24 row for each hour and every line will be one day's information
0
 
Kevin CrossChief Technology OfficerCommented:
Okay, then you want:
CREATE  TABLE `recorder_name` (
  `date` DATE NOT NULL,
  `0` INT NULL ,
  ...,
  `23` INT NULL,

PRIMARY KEY (`date`) );

When building the string, you can additional tighten the code by using a loop to do the 0 - 23 column entries, but even if you type them in manually (using copy and paste), you can set the recorder_name value using the variable and then execute the SQL string per the references I gave you previously.  They also give you syntax for creating procedure -- give it a try and post back if you get an error -- think this will give you a better understanding of the code if you try it out yourself.
0
 
Kevin CrossChief Technology OfficerCommented:
Another note, you will want to set the data type of `0` to `23` columns to what is appropriate -- showed with INT as an example.  For `date` the DATE data type should be good since you just want the date and don't need the time portion which you are breaking up into each hour.
0
 
3XLcomAuthor Commented:
but i want to do it with a loop and check if table exist also
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, sorry I left that part out accidentally.

Change:
CREATE TABLE

to:
CREATE TABLE IF NOT EXISTS
0
 
3XLcomAuthor Commented:
yes but i want to know this if it is exist i mean procedure will give me a spesific answer

I have it
I do it

sth. like this

so i need an if statement for create table and need a loop for 24 hours' rows
0
 
Kevin CrossChief Technology OfficerCommented:
Not sure I understood what you last post is trying to say.  Based on your question and details you have given me, I have provided you with code you will need.  If you are having a specific error, please post the code you have and error you are getting !
0
 
3XLcomAuthor Commented:
Please check the question carrefully
i write on there at the end :
it will also will check if table already exist and will return a spesific error code or succes

so i need to check if table exist and return a value if it exist so instead of this :

CREATE TABLE IF NOT EXISTS

i need another if statement which will produce an answer to stored procedure

and also i ask for a loop not for 24 create row command
0
 
Kevin CrossChief Technology OfficerCommented:
http:#a33730149 << mentioned using a WHILE loop
http:#a33728786 << has code examples of IF, dynamic SQL (prepared statements)

I guess you are wanting someone to write all the code for you ?
0
 
Kevin CrossChief Technology OfficerCommented:
Here is the WHILE loop manual >> http://dev.mysql.com/doc/refman/5.0/en/while-statement.html
Again, I will say, I did NOT intend on writing the initial code for you as you will (1) learn nothing that way and (2) have to modify it all anyway to fit your actual data types and business logic you want.  You need to take what I have told you and try this for yourself and then post back if you have an issue.  
0
 
Kevin CrossChief Technology OfficerCommented:
3XLcom,

Looking back, I realize you may be having difficulty even getting started, so here is a stub of what the procedure looks like.  Once you have had a chance to add-in your code -- then post back issues you are having.

You call it like this:
call create_recorder_tbl('recorder', @message);
select @message;

You may want to consider a function which returns a varchar so you don't have to select the output variable as a separate step.
DROP PROCEDURE IF EXISTS create_recorder_tbl;
DELIMITER $$
CREATE PROCEDURE create_recorder_tbl(
   IN tablename VARCHAR(255), 
   OUT message VARCHAR(255)
) BEGIN

-- check if table exists using information schema
IF EXISTS(
      SELECT null
      FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = tablename
      -- AND SCHEMA_NAME = 'your_database_name'
   ) THEN
   -- send error/info message that table exists.
   SET message = CONCAT(tablename, ' already exists!');
ELSE
   -- Build dynamic SQL string.
      -- Create base string.
      -- Append `0` - `23` columns via loop.
      -- Append end portion of create DDL.

   -- Execute dynamic SQL string.
   PREPARE dySQL FROM @sqlstring;
   EXECUTE dySQL;
   DEALLOCATE PREPARE dySQL;
   
   -- set success/info mesage.
   SET message = CONCAT('Successfully created ', tablename, '!');
END IF;

END$$
DELIMITER ;

Open in new window

0
All Courses

From novice to tech pro — start learning today.