using dynamic sql to write a procedure for table creation

[narayana]

hi friends,
i want to create a procedure like when i give a name as parameter to
the procedure it should create a table with that name and with two
column names as any type. plz explain it with dynamic sql.

[Knut Stolze]

narayana wrote:

hi friends,
i want to create a procedure like when i give a name as parameter to
the procedure it should create a table with that name and with two
column names as any type. plz explain it with dynamic sql.

Construct a string (VARCHAR) that contains the CREATE TABLE statement and
then call EXECUTE IMMEDIATE.

CREATE PROCEDURE create_tab ( tab IN VARCHAR(128) )
BEGIN
DECLARE stmt VARCHAR(1000);
SET stmt = 'CREATE TABLE ' || tab ||
' ( id INT NOT NULL PRIMARY KEY )';
EXECUTE IMMEDIATE stmt;
END@

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

narayana wrote:

hi friends,
i want to create a procedure like when i give a name as parameter to
the procedure it should create a table with that name and with two
column names as any type. plz explain it with dynamic sql.

Google for my name and PREPARE or EXECUTE I have posted more examples
that I remember here....

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[--CELKO--]

Why are you doing this?? One of the most basic ideas of RDBMS is that
you have a data model of a real world which is represented by tables in
a schema. To create tables with procedures on the fly woudl be like
having elephants drop out of the sky.

These magical tables clog up the schema and destroy the data model. ,
area screaming pain to maintain, etc.

[narayana]

no not that ,
when the situation demands with in the application programs then what
is the way .

According to the basics, dml statements are not recommended in
procedures.

then there is only one way that is dynamic sql with dml execution in
procedure

are you agreee ?

[--CELKO--]

NO. Application code does not drive the data model. The data model
comes frist and then the application is built on the model. You should
know all the tables in the schema when you start.

Newbies will mimic scratch files with tables that are created dynamic
SQL because they are used to coding file systems that work that way.

[Brian Tkatch]

The requirements come first, the specifications second. The
requirements pretty much define the application while the specification
defines the data model.

A business does not care how the data is stored, so long as the
application can use it. In fact, the data is stored most efficiently to
be accessed by the program. Warehouse or OLTP is defined by the
application, the database simply implements it. If the application
needs a query, the database makes it work.

The data model does, however, define efficiencies, security, and
relationships. In these areas, the database comes first and the
application second (well, in theory at least).

---

While i agree that it is rare that an application should do DDL, it is
not taboo. There are times when it is the best way to do things. For
example, if a file is to be imported for known columns, but the order
of the columns in the file is not known, an application can CREATE a
TABLE on the fly to hold the imported data in original form and then
have a PROCEDURE access the appropriate data.

Or, for backup purposes, having a monthly process CREATE a history
TABLE with the year and month included in the TABLE's name, moving the
historical data to it, and finally archiving it.

--

If you really want to help, you should first answer the question(s),
and then mention that it is unlikely that an application should do DDL,
both from an efficiency and from a data design standpoint. But to say
"Why are you doing this??" or scream out "NO." and then give your own
personal beliefs on design theory is pedantic and unwelcome.

B.

[--CELKO--]

>> While i agree that it is rare that an application should do DDL, it is not taboo.<<

Yes, it is. This is foundations, not a matter of personal style.

For example, if a file is to be imported for known columns, but the order of the columns in the file is not known, an application can CREATE a TABLE on the fly to hold the imported data in original form and then have a PROCEDURE access the appropriate data. <<
I would think that you might want to use an ETL tool for that kind of
thing. And that would be done **outside** of the schema. Ordering is
a physical problem, not a data model or logical problem. Likewise,
converting units from SI to English, Dewey Decimal to LOC, etc. are
physical format problems. Why do you think otherwise?
If you really want to help, you should first answer the question(s), and then mention that it is unlikely that an application should do DDL, both from an efficiency and from a data design standpoint. <<

After teaching SQL for 20+ years and writing six books on SQL, I
disagree. When someone has missed the principles, lecture them about
those principles. Most of the time, ifyou can see what their mental
model was and why they made an error, then you can REALLY help them.
There is a literature on "How we know what is not so" in critical
thinking. Most sane people work from a metnal model and do not behave
at random.

I then suggest a better answer, based on the principles. My "Zen
Master with a stick approach" bothers the current generation of whining
kids with a sense of entitlement. But I have warched this lack of
professionalism kill people (Google some of my posting about a flawed
African medical relief database on other newsgroups; the database was
designed for the front end form screens wthout a real data model).

Bad programmers kill people.

If you give someone a Kludge first, that is all they will hear and that
is what they will do from that time forward. If this was a fine
furniture newsgroup and someone asked for the best kind of rocks to use
for pounding screws into wood, you can answer "Granite!! Bigger chunks
work faster!" or you can answer "NO! Let me tell you about screws.
They can be more complicated than rocks, but they are worth the effort
and the learning curve. etc."