Use Dynamic Create Table Command in Stored procedure
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
Software/Hardware used:
Software/Hardware used:
ASKED:
April 23, 2007 1:24 PM
UPDATED:
April 23, 2007 3:07 PM
Answer Wiki:
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)
To see all answers submitted to the Answer Wiki: View Answer History.
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