I'm trying to get working something like :

SCRIPT
BEGIN

EXECUTE IMMEDIATE ' CREATE PROCEDURE Customer_Fix
BEGIN
   -- SQL to clean up data
END
';

CALL Customer_Fix();

END

I've tried a few variations, but think i need the guidance of the EDB enlightened...
thanks in advance

The procedure name needs to be in double quotes and the "SQL to clean up
data" needs to be in a string with two single-quotes on each end and
launched with EXECUTE IMMEDIATE.

Try creating it manually in EDB Manager, then looking at the SQL History.
You'll see something like this:

SCRIPT
BEGIN

EXECUTE IMMEDIATE 'CREATE PROCEDURE "Customer_Fix"
BEGIN
 EXECUTE IMMEDIATE ''--SQL to clean up data'';
END
;

END


David Cornelius
Cornelius Concepts

Thanks for your quick response David

I tried your suggestion (i.e. modified the CREATE PROCEDURE call to have an internal 'EXECUTE IMMEDIATE' and double up all the internal single quotes, but am still getting the same error when i try to call that Stored Proc :

CALL Customer_procFix(); -- this gets the error
ElevateDB Error #700 An error was found in the script at line 66 and column 6 (ElevateDB Error #401 The procedure Customer_procFix does not exist in the schema Default)

EXECUTE IMMEDIATE 'CALL Customer_procFix();';
gets :
ElevateDB Error #700 An error was found in the statement at line 66 and column 19 (Expected SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, BACKUP, RESTORE, SET BACKUPS, MIGRATE, REPAIR, VERIFY, OPTIMIZE, IMPORT, EXPORT, DISCONNECT, REMOVE, PUBLISH, UNPUBLISH, SAVE, LOAD, SET UPDATES, COPY FILE, RENAME FILE, DELETE FILE but instead found CALL)

just in case you misundertood, I am trying to create AND invoke a stored procedure within a single invocation of an SQL script (to minimise the steps our operations people will have to perform on customer sites). I think i am OK with creating the stored proc (only).

TonyWood,

> I've tried a few variations, but think i need the guidance of the EDB enlightened...
> thanks in advance

This was discussed earlier: have a look here:

http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_sql&page=1&msg=4559#4559

Concerning the syntax a quotation by Tim:

"Procedure and function calls in EDB's SQL/PSM are bound during
compilation time, meaning they do not require an EXECUTE IMMEDIATE or
escapement.  Only DML and DDL statements are executed dynamically. "

Regards Uli

Tony


There was another post about this a while ago. I can't remember the outcome, and a simple search for "stored procedure" didn't find it so you'll have to search for it yourself.

Roy Lambert [Team Elevate]

Uli


That's the one I was trying to remember.

Roy Lambert

Thanks for the responses fellas, i guess i have to split it up into multiple scripts.

Tony,

<< I've tried a few variations, but think i need the guidance of the EDB
enlightened... thanks in advance >>

The short answer is no - you can't create a new procedure/function and
reference it in the same script/routine.  Procedures/functions are somewhat
unique in that they are statically bound when the outer routine is
compiled/prepared.  Therefore, they must exist at the time of compilation.

--
Tim Young
Elevate Software
www.elevatesoft.com