Is it possible to use a stored procedure to create a dynamic table name based on the name passed to the stored proc?

e.g.,

create procedure mktbl(name varchar(10))
begin
create table qgpl.&name(
col01 numeric(10)
);

end;

Yes. Use dynamic SQL to do this.

DECLARE STMT varchar(5000);
SET STMT = 'CREATE TABLE ' || TableParm || ...
PREPARE s1 for STMT;
EXECUTE s1;

Andy

That worked perfectly.... thank you very much!!!

Will I need to use the Prepare/Execute method for inserting/updating tables based on a dynamic name, as well?

Also thought of an issue where I have prepared a CURSOR that references a particular table.... How do I use the Prepare/Execute method, or will I even need to?

Thank you....

Just as a shortcut, you can use
DECLARE STMT varchar(5000);
SET STMT = 'CREATE TABLE ' || TableParm || ...
EXECUTE IMMEDIATE stmt;
to cut out an entire SQL statement. Personally I only use prepare and execute as two statements if I am going to re-issue the dynamic statement or have parameter markers in the statement.

For insert and update you use either execute immediate or prepare and execute. If you are going to be issuing either insert or update more than once you should probably use parameter markers, prepare once and execute often.

For a cursor use something like:
DECLARE SEL_STMT varchar(5000);
DECLARE C1 CURSOR FOR S1;
SET STMT = 'SELECT ... ';
PREPARE S1 FROM SEL_STMT;
OPEN C1;
FETCH C1 INTO ...;

James Campbell

Okay.... I think I'm getting there (thanks to Andy and James), but I am having one more problem.

How do I include strings in my stmt?

For instance,
stmt = SELECT 'Y' FROM table
WHERE col01 = 'someValue'

The ' are causing me problems and I can not figure out how to get around it. In many programming languages it's as simple as using two ', but that's not working for me.

I've also seen online an option to use a cursor and do the following:
DECLARE c1 CURSOR x
stmt = 'SELECT ? FROM table
WHERE col01 = ?';
PREPARE x FROM stmt;
OPEN c1 USING 'Y', 'someValue';

BUT... Couldn't seem to get this to compile, either.

Thank you!!!

I was actually able to get everything to work.... Not sure why it wasn't working last night, but guess I needed to take a break.

Anyway, passing a string is as simple as...

stmt = 'SELECT ? FROM table
WHERE col01 = ?';

PREPARE s1 FROM stmt;

EXECUTE s1 USING 'Y', 'SomeValue';

Thanks for all of the help!!!

Just to expand your expertise: When DB2 is preparing your statement it doesn't know what datatypes you are going to be presenting. Therefore it generates a generic access path - you'll have to do an explain to determine just how it handles easch statement. But as an example if col01 is in an index DB2 might end up doing an index scan rather than an index lookup.

You can give DB2 a boost by using a typed parameter marker:
stmt = 'SELECT ? FROM table WHERE col01 = cast(? as varchar(10))';
for example - where the datatype in the cast is the datatype of col01. "This notation is not a function call, but a "promise" that the type of the parameter at run time will be of the data type specified or some data type that can be converted to the specified data type."

This might give DB2 the information it needs to give you a better access path.

James Campbell

Thanks for all of your help, James. BTW, do you know if there is a limitation on the # of input parameters that can be passed in one statement? I have a statement in particular that will require about 13 parameters.

I'm surprised there isn't an easier (excuse my laziness ) to build a dynamic query while doing string comparisons. Now I wish all of my comparisons were based around numbers.

The only realistic limit I've seen is "Maximum total length of host and indicator variables pointed to in an SQLDA: 32767 bytes"

Unlikely you'll reach that limit.

James Campbell