Hi Team,
I have this Statement which works fine in EDBManager.
{sql}
CREATE PROCEDURE ArchiveJobTickets(IN dEndDate Date, OUT RecCount Integer)
BEGIN

DECLARE DupCount INTEGER;
DECLARE Checker STATEMENT;
DECLARE InsertStmt Statement;
DECLARE DeleteStmt Statement;

PREPARE InsertStmt from 'insert into dbiarchive.ajobtickets select * from dbiworkflow.jobtickets
                          where jobstatus in (''CL'', ''CA'', ''SC'') and cast(Created as Date) < ? AND JobNo NOT IN (SELECT JobNo FROM dbiarchive.AJobTickets)';
PREPARE DeleteStmt from 'delete from dbiworkflow.JobTickets where jobstatus in (''CL'', ''CA'', ''SC'') and cast(Created as Date) < ?';

EXECUTE InsertStmt USING dEndDate;

SET RecCount = RowsAffected(InsertStmt);

execute DeleteStmt USING dEndDate;

END
{sql}
I wanted to put it, and others, into a Script, so I tried this:
{sql}
SCRIPT
BEGIN

CREATE PROCEDURE ArchiveJobTickets(IN dEndDate Date, OUT RecCount Integer)
BEGIN

DECLARE DupCount INTEGER;
DECLARE Checker STATEMENT;
DECLARE InsertStmt Statement;
DECLARE DeleteStmt Statement;

PREPARE InsertStmt from 'insert into dbiarchive.ajobtickets select * from dbiworkflow.jobtickets
                          where jobstatus in (''CL'', ''CA'', ''SC'') and cast(Created as Date) < ? AND JobNo NOT IN (SELECT JobNo FROM dbiarchive.AJobTickets)';
PREPARE DeleteStmt from 'delete from dbiworkflow.JobTickets where jobstatus in (''CL'', ''CA'', ''SC'') and cast(Created as Date) < ?';

EXECUTE InsertStmt USING dEndDate;

SET RecCount = RowsAffected(InsertStmt);

execute DeleteStmt USING dEndDate;

END;

END
{sql}

Nope: Got this:  "ElevateDB Error #700 An error was found in the script at line 4 and column 8 (Expected : but instead found PROCEDURE)"

I have tried several permutations but can't get it to Prepare/Execute as a script.

Thoughts/suggestions appreciated.

Regards & TIA,
Ian

Solved.
Don't know why the ':' issue, but I have it sorted now.  Needed double double single quotes areound the CL, CA & SC.  i.e.  ''''CL''''.

Ian

Ian,

Here it is, *untested*, I might have typed something wrong while formatting, but it shows how to do it:

SCRIPT
BEGIN
  EXECUTE IMMEDIATE '
    CREATE PROCEDURE ArchiveJobTickets(IN dEndDate Date, OUT RecCount Integer)
    BEGIN
      DECLARE DupCount INTEGER;
      DECLARE Checker STATEMENT;
      DECLARE InsertStmt Statement;
      DECLARE DeleteStmt Statement;
    
      PREPARE InsertStmt FROM ''
        INSERT INTO dbiarchive.ajobtickets
          SELECT
            *
          FROM
            dbiworkflow.jobtickets
          WHERE
            jobstatus IN (''''CL'''', ''''CA'''', ''''SC'''') AND
            CAST(Created AS Date) < ? AND
            JobNo NOT IN (SELECT JobNo FROM dbiarchive.AJobTickets)
      '';
    
      PREPARE DeleteStmt FROM
        ''DELETE FROM dbiworkflow.JobTickets WHERE jobstatus IN (''''CL'''', ''''CA'''', ''''SC'''') AND CAST(Created AS Date) < ?'';
    
      EXECUTE InsertStmt USING dEndDate;
      SET RecCount = RowsAffected(InsertStmt);
      EXECUTE DeleteStmt USING dEndDate;
    END
  ' ;
END

--
Fernando Dias
[Tem Elevate]