Greetings All,

Firebird v 2.5.4

Many times I would like to run a report before I do an update.  Why can’t I allow for both in one stored procedure?  Look at the end of this stored procedure where I use V_REPORT.

Thanks,

Mike

SET TERM ^^ ;

CREATE PROCEDURE X_CHK_LEGAL_CASE_DATE (

  V_REPORT SmallInt)

returns (

  ACCT_ID Integer,

  CASE_ID SmallInt,

  LEGAL_CASE_DATE Date,

  CASE_LEGAL_CASE_DATE Date,

  ACCH_LEGAL_CASE_DATE Date,

  ACCH_NOTE VarChar(200))

AS

DECLARE VARIABLE iAcctCaseCourtID Integer;

begin

  FOR SELECT ACCT_CASE_COURT_ID,

             CAST(CREATE_DATE AS DATE),

             ACCT_ID,

             CASE_ID

        FROM ACCT_CASE_COURT

       WHERE STATUS_CODE = 'A'  

    ORDER BY ACCT_ID, CASE_ID

        INTO :iAcctCaseCourtID, :LEGAL_CASE_DATE, :ACCT_ID, :CASE_ID DO

    BEGIN            

      SELECT LEGAL_CASE_DATE

        FROM ACCT_CASE

       WHERE ACCT_ID = :ACCT_ID

         AND CASE_ID = :CASE_ID

        INTO :CASE_LEGAL_CASE_DATE;

      IF (CASE_LEGAL_CASE_DATE IS NULL) THEN   

        BEGIN

          SELECT FIRST 1 CAST(CREATE_DATE AS DATE), NOTE

            FROM ACCT_CASE_COURT_HIST

           WHERE ACCT_CASE_COURT_ID = :iAcctCaseCourtID

           ORDER BY ACCT_CASE_COURT_HIST_ID  

            INTO :ACCH_LEGAL_CASE_DATE, ACCH_NOTE;

          IF (V_REPORT = 1) THEN 

            SUSPEND;

          ELSE       

            UPDATE ACCT_CASE

               SET LEGAL_CASE_DATE = :ACCH_LEGAL_CASE_DATE

             WHERE ACCT_ID = :ACCT_ID

               AND CASE_ID = :CASE_ID;

        END   

    END         

end ^^

SET TERM ; ^^