I meant “However, If I comment out the first portion (as shone below) leaving just the UPDATE clause it works fine and the “Commit” and “Rollback” buttons ARE enabled”
From: [email protected] [mailto:[email protected]] Sent: Tuesday, November 03, 2015 10:10 AM To: [email protected] Subject: RE: [firebird-support] Why can't I have a SUSPEND or UPDATE in the same Stored Procedure? I’m sorry I should have been a little bit more concise on what I’m doing. I use Database Workbench v5 for all of my development needs. Running the stored procedure setting the V_REPORT = 1 in Database Workbench, I do get a results set. So far, so good. However, if I set V_REPORT = 0 which should cause the UPDATE to be processed instead, I’m not able to commit as the “Commit” and “Rollback” buttons are not enabled. However, If I comment out the first portion (as shone below) leaving just the UPDATE clause it works fine and the “Commit” and “Rollback” buttons are not enabled. /* 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; Any ideas why? Thanks, Mike From: [email protected] [mailto:[email protected]] Sent: Tuesday, November 03, 2015 9:29 AM To: [email protected] Subject: ODP: [firebird-support] Why can't I have a SUSPEND or UPDATE in the same Stored Procedure? hi, you got an error or what? regards, Karol Bieniaszewski -------- Oryginalna wiadomość -------- Od: "'stwizard' [email protected] [firebird-support]" <[email protected]> Data: 03.11.2015 14:59 (GMT+01:00) Do: [email protected] Temat: [firebird-support] Why can't I have a SUSPEND or UPDATE in the same Stored Procedure? 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 ; ^^ [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
