I have a script where I am creating a procedure and then trying to use it.  When I execute the script I get error #700 telling me that the procedure doesn't exist.

Here is an stripped down example

SCRIPT
BEGIN
 EXECUTE IMMEDIATE 'CREATE PROCEDURE TestProc
   BEGIN          
     EXECUTE IMMEDIATE ''CREATE INDEX ABC ON DEF (ABC)'';
   END';
 CALL TestProc('Table1');
END

I run it and get
ElevateDB Error #700 An error was found in the script at line 8 and column 8 (ElevateDB Error #401 The procedure TestProc does not exist in the schema Default)

It looks like it is preprocessing the script and telling me that TestProc doesn't exist before actually trying to execute the script, in which case it would have been present by time the Call was made.

How should I deal with this? The script is a series of updates to the database procedures and indexes, etc.  I would prefer to keep it in one overall script if possible

Todd,

The only way I know to avoid the error is to have the statement to create the procedure and the statement to execute the procedure in different scripts.

It is worthwhile creating functions such as ProcedureExists (and TableExists, IndexExists and so on) to test if the procedure has already been created.

Richard Harding

Todd,

<< I have a script where I am creating a procedure and then trying to use
it.  When I execute the script I get error #700 telling me that the
procedure doesn't exist. >>

Certain things like procedure and function calls are bound at compile time
for the script, and therefore need to exist at that point.  USE for
databases is another statement that requires this.

<< How should I deal with this? The script is a series of updates to the
database procedures and indexes, etc.  I would prefer to keep it in one
overall script if possible >>

You're going to need to split the DDL updates for the database into one
script, and the calls to these newly-created functions/procedures in another
script.

--
Tim Young
Elevate Software
www.elevatesoft.com