Creating an SQL procedure seems to check whether the statements w ill work

Hi list,

I'm trying to create an upgrade function using SQL. I want it to be
re-runnable, such that if a client has run it over their database, they can
run it again without harm, and without it failing.

I need to add columns to a table, so I want to do an ALTER TABLE ... ADD
COLUMN. But first I want to check whether the column already exists on the
table. The only way I've found to do that is to interrogate the SYSCOLUMNS
view. Now you can't so that in a script run with RUNSQLSTM, but you can do
it in a stored procedure.

So my upgrade strategy involves a CL which uses RUNSQLSTM to create a
procedure, then calling that procedure via RPG and embedded SQL (this last
twist is to allow me to pass parameters to the stored procedure, which you
can't do from CL).

It works OK when the new columns don't exist. But when I try running it
again, I get an error on creating the stored procedure. Not running it:
creating it. And the creation fails because the new column already exists.

Which strikes me as bizarre. It seems that the SQL compiler actually tries
to run the statements that comprise the script, not just syntax check them.
Is this normal behaviour, and can anyone think of a way round it?

Here's a cut-down version of the script that creates the procedure; note
that I've tried various ways of testing for the (non-) existence of the
column, including using the EXISTS and IN predicates. They all give the
same error, which is also shown below. We're at V5R4.


CREATE PROCEDURE damm4db.UPGRADEPRC (
IN DB CHAR(10),
IN ARC CHAR(10)
)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN

/* Only add these columns if they're not there... */

DECLARE COLNAME CHAR(130);

SELECT
COLUMN_NAME
INTO
COLNAME
FROM
damm4db.SYSCOLUMNS
WHERE
TABLE_NAME = 'MESSAGES' AND COLUMN_NAME = 'ELECTRONICBROKERID';

IF
COLNAME IS NULL OR COLNAME = ''
THEN
ALTER TABLE
damm4db.MESSAGES
ADD
ELECTRONICBROKERID
FOR COLUMN ELECBROKID VARCHAR (32) ;
END IF;

END

From the end of the generated listing:

SQL0612 30 66 Position 9 ELECTRONICBROKERID is a duplicate
column name.

Cheers and TIA,

Martin.