At 02:01 AM 15/07/2005 +0000, you wrote:

>I need to execute the ALTER PROCEDURE code at runtime using Delphi SQL
> statements (I cannot execute it directly from SQL editor due to table
>integrity issues). The code below produces an error: ("SQL error code
>= -104 Token unknown.. SET'...) on line 2, when executed at runtime.
>What would be the correct method of writing this code using Delphi SQL
>statements ?

What you are doing here is that you are trying to push a script (a batch of
statements) into an object that was designed to wrap a single DSQL statement.

ALTER PROCEDURE is a single statement with a complex syntax. It consists
of a header (that starts with ALTER PROCEDURE) and a body (that starts with
the keyword BEGIN and ends with the keyword END). Between these two
keywords, the body is made up of one or more statements, which can be
themselves grouped within embedded logical BEGIN...END blocks. Each
enclosed statement has to be terminated by a semi-colon.

To submit your ALTER PROCEDURE DDL statement dynamically, i.e. via a
Delphi statement object, submit only that statement:

Transaction.StartTransaction;
SQL.Clear;
SQL.Add('ALTER PROCEDURE GETSTDDEV(FIRSTDATE TIMESTAMP)');
SQL.Add('RETURNS(AVGINDEX REAL,COUNTINDEX INTEGER,STDDEV REAL)');
SQL.Add('AS');
SQL.Add('begin');
/* Make sure you put the enclosed statement terminators in!! */
SQL.Add('select avg(xindex), count(xindex) from xtable;');
...
SQL.Add('end');
ExecQuery;
Transaction.Commit;

I hope you are also building exception handling into your routine! Also
bear in mind that there will be concurrency issues if you try to do this
operation without an exclusive connection.

./heLen