Subject Re: [IBO] Stored procedure, ExecSQL and Suspend
Author Marco Menardi
well, I've tried with a different SP, that does not have FOR SELECT..., it does a select and puts the value in the return variables.
No, without the final SUSPEND it does not work (return values) using IB_StoredProc.
So, or it's a IB_StoredProc bug, or there is something we are missing...
thanks
Marco Menardi

CREATE PROCEDURE GET_CONDOMINIO_INFO (
IN_CONDOMINIO_ID VARCHAR (6),
IN_ESERCIZIO_ID VARCHAR (6))
RETURNS (
OUT_CONDOMINIO_ID VARCHAR (6),
OUT_DESCRIZIONEDISP VARCHAR (30),
OUT_AMMINISTRATORE_ID CHAR (1),
OUT_DATA_CHIUSURA VARCHAR (4),
OUT_NUM_EDIFICI INTEGER,
OUT_NUM_SCALE INTEGER,
OUT_PC_DIFF_GR CHAR (1))
AS
BEGIN
OUT_NUM_EDIFICI = 0;
OUT_NUM_SCALE = 0;
OUT_CONDOMINIO_ID = NULL;

SELECT CONDOMINIO_ID,
DESCRIZIONEDISP,
AMMINISTRATORE_ID,
CHIUSURA_GG_MM
FROM CONDOMINI
WHERE (CONDOMINIO_ID = :IN_CONDOMINIO_ID)
INTO
:OUT_CONDOMINIO_ID,
:OUT_DESCRIZIONEDISP,
:OUT_AMMINISTRATORE_ID,
:OUT_DATA_CHIUSURA;

IF (OUT_CONDOMINIO_ID IS NOT NULL) THEN
BEGIN
SELECT COUNT(*)
FROM EDIFICI
WHERE (CONDOMINIO_ID = :OUT_CONDOMINIO_ID)
INTO
:OUT_NUM_EDIFICI;

SELECT COUNT(*)
FROM SCALE
WHERE (CONDOMINIO_ID = :OUT_CONDOMINIO_ID)
INTO
:OUT_NUM_SCALE;

OUT_PC_DIFF_GR = 'N';
IF (EXISTS (SELECT * FROM PIANO_CONTI
LEFT JOIN GET_CONTO_DIFFERENZIA_GR(PIANO_CONTI.CONTO_ID, :IN_CONDOMINIO_ID, :IN_ESERCIZIO_ID,
PIANO_CONTI.DIFFERENZIA_GRUPPO_RIPARTO) ON (1=1)
WHERE (CONDOMINIO_ID = :IN_CONDOMINIO_ID) AND
(ESERCIZIO_ID = :IN_ESERCIZIO_ID) AND
(GET_CONTO_DIFFERENZIA_GR.OUT_DIFFERENZIA='S'))) THEN
OUT_PC_DIFF_GR = 'S';
SUSPEND;
END
END