There are a number of areas where something slightly unusual works in
Firebird but Database Workbench can't cope with it.
Have you tried running it by typing EXECUTE PROCEDURE into isql?
On 03/11/2015 16:09, 'stwizard' stwiz...@att.net [firebird-support] wrote:
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: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: Tuesday, November 03, 2015 9:29 AM
To: firebird-support@yahoogroups.com
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' stwiz...@att.net [firebird-support]"
<firebird-support@yahoogroups.com>
Data: 03.11.2015 14:59 (GMT+01:00)
Do: firebird-support@yahoogroups.com
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]
--
Tim Ward
