In SQl Server its possible to concoct a string representing a sql command and then execute it within a stored procedure. This overcomes the problem of receiving 'peices' of a command as a parameter, such as a list of values for an IN phrase, because it allows you to merge them with the string version of the command.

Does DB2 enable this?

Thanks,

Kimball Johnson

Yes,
Look at the PREPARE and EXECUTE SQL commands.

Andy

The DB2 help file says this about EXECUTE and EXECUTE IMMEDIATE:

"This statement can be embedded only in an application program."

My question was about using such a command within a 'stored procedure'.

Is the answer still yes?

Thanks,

Kimball Johnson

Yes, This will work in an SP:

DECLARE stmt VARCHAR(2000);
...

SET STMT = 'SELECT * from MyTable where ' || colname || ' in ' || list;
PREPARE S1 FROM stmt;
EXECUTE S1;
...

Andy

Great! Thanks!

What about the need for cursors?

When you EXECUTE the string version of the command, where do the records appear?

Do you have to declare a cursor for return to client? And then Open it?

Does the execute command return a table so that you could say,

SET something = EXECUTE "select * from mytable" - ?

?

Thanks,

Kimball Johnson

Sorry, forgot about cursor.

DECLARE MyCursor CURSOR for S1;
...
DECLARE stmt VARCHAR(2000);
...
SET STMT = 'SELECT * from MyTable where ' || colname || ' in ' || list;
PREPARE S1 FROM stmt;

Open MyCursor;
...

Andy

Should be something like this:-

DECLARE stmt VARCHAR(2000);
DECLARE vchar1 varchar(100);
...

SET STMT = 'SELECT vchar1 from MyTable where ' || colname || ' in ' || list;
PREPARE S1 FROM stmt;
DECLARE C1 CURSOR FOR S1;
OPEN C1;
FETCH C1 INTO vchar1;
....

Just google for some examples