Use Dynamic Create Table Command in Stored procedure

200 pts.
Tags:
Application development
AS/400
SQL
Does any one know how to pass a library and table name variable to the SQL CREATE TABLE Command? I need to be able to build the tables on the fly and the name needs to be different each time the table is created. I am calling the SQL Command from within a Stored Procedure. Thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

I’ll assume that you’re working on DB2 on an AS400. The answer will be similar, but slightly different in other configurations.

If the stored procedure is written in a compiled language, such as C, PL/1, or RPG, assemble the SQL statement to be executed into a variable, then execute the variable. Here’s a C example:

char buffer[256];
sprintf(buffer, “CREATE TABLE %s/%s(x INT, y INT)”, libName, tableName);
EXEC SQL EXECUTE IMMEDIATE :buffer;

If you’re coding your stored procedure in SQL, make a separate stored procedure in a compiled language and call it from your original stored procedure.

Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • FCARPENTER
    I'm sorry yes it is the AS/400 and DB2. I appreciate your reply. I have already coded a similar work around. So you answer confirms I am taking the appropriate action and that there is no way to directly enter a variable into the Create Table Command. Thank you
    200 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following