Solved

create table procedure

Posted on 2010-09-21
14
385 Views
Last Modified: 2012-05-10
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 ...
0
Comment
Question by:3XLcom
  • 10
  • 4
14 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33728769
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33728786
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
 

Author Comment

by:3XLcom
ID: 33729116
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33730149
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33730155
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
 

Author Comment

by:3XLcom
ID: 33730162
but i want to do it with a loop and check if table exist also
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33730163
Yes, sorry I left that part out accidentally.

Change:
CREATE TABLE

to:
CREATE TABLE IF NOT EXISTS
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:3XLcom
ID: 33730254
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33730269
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
 

Author Comment

by:3XLcom
ID: 33730280
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33730365
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33730383
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33730741
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 33766579
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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Left Join with Tuple returning more rows 10 90
SQL join help to a thrid table 51 76
Strange msg in the SSMS pane 13 49
T-SQL Update Table using Input Parameter as Column Name 6 40
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now