🚀 go-pugleaf

The procedure below is designed to return fields from a table 'L' under two conditions, depending on the input parameter 'EXCLUDE'.  If exclude is 1 then the row is returned only if a
matching row in the ATTRIBUTE table is not present, and if zero, returns only if a matching row is present.  I figured that could use IF/THEN statements and SUSPEND to return the data
selectively.  However, despite the fact that I know the correct data is present, the SP does not return any results for the EXCLUDE=0 case, which leads me to suspect that output parameters
are not being set for every iteration of the loop.

The SELECT(*) COUNT FROM ATTRIBUTE statement uses values stored in the OUTPUT parameters.  Are those values present regardless as to whether SUSPEND is called or not, or are they only set
when SUSPEND is called?

Can anyone shine any light on what might be going on here, or suggest an alternative approach?

Many thanks for your help.


CREATE PROCEDURE SAMPLE_QUERY QUERYVALUE VarChar(60) CHARACTER SET ISO8859_1, EXCLUDE Integer, ATTNAME VarChar(60) CHARACTER SET ISO8859_1) returns (LOCATION_ID Integer, LOCATION_NAME
VarChar(60) CHARACTER SET ISO8859_1, COUNTY_NAME VarChar(60) CHARACTER SET ISO8859_1, REGION_NAME VarChar(60) CHARACTER SET ISO8859_1) AS

DECLARE VARIABLE attid INTEGER;
DECLARE VARIABLE attcount INTEGER;

BEGIN
    FOR SELECT LOCATION_ID, LNAME, CNAME, RNAME FROM  L  WHERE LNAME  LIKE :QUERYVALUE
    INTO :LOCATION_ID, :LOCATION_NAME, :COUNTY_NAME, :REGION_NAME DO
    BEGIN
       SELECT ATTRIBUTE_ID FROM ATTRIBUTELIST WHERE ATTRIBUTE = :ATTNAME INTO :attid;
       SELECT COUNT(*) FROM ATTRIBUTE WHERE LOCATION_ID = :LOCATION_ID AND ATTRIBUTE_ID = :attid INTO :attcount;
       IF (EXCLUDE = 1) THEN
       BEGIN
           IF (attcount = 0) THEN
           BEGIN
               SUSPEND;
           END
      END
      ELSE
      BEGIN
          IF (attcount > 0) THEN
          BEGIN
              SUSPEND;
          END
      END
    END
 END