CREATE PROCEDURE Phtotgraph_Summary_SP(photograph_classification LIKE fotografs.classificacio_fotograf)
RETURNING CHAR(100) as Photograph, INTEGER as total_Expositions, CHAR(50) as record_Exposition, INTEGER as num_photos


DEFINE photographCode varchar(8);
DEFINE photographName varchar(100);
DEFINE totalExpositions int;
DEFINE averagePhotos decimal(5,2);
DEFINE recordExposition  varchar(50);
DEFINE numPhotos  int;
DEFINE recordExpositionCode varchar(10);
DEFINE rowcount int;


FOREACH cursor
        FOR SELECT  ph.passaport,ph.nom, count(distinct ex.codi_exposicio) INTO photographCode,photographName, totalExpositions
        FROM fotografs ph, exposen ex
        where ph.passaport = ex.passaport
        group by ph.passaport, ph.nom                           
        
        rowcount=0;
        FOREACH cursor2 FOR
            Select max(ex.num_fotos) INTO numPhotos from exposen ex, Exposicions exp , fotografs ph
                where ex.codi_exposicio = exp.codi_exposicio and ph.passaport = ex.passaport and ph.passaport = photographCode;
            rowcount=rowcount+1;
            select exp.titol INTO recordExposition from  exposen ex, Exposicions exp , fotografs ph
                    where ex.codi_exposicio = exp.codi_exposicio and ph.passaport = ex.passaport and ph.passaport = photographCode
                    group by exp.titol
                    having max(ex.num_fotos)= numPhotos;
       END FOREACH;
       IF rowcount>1 THEN
              recordExposition = 'Gamarus';
              CONTINUE FOREACH;
       ELSE
              recordExposition= recordExposition;
       END IF;
    RETURN photographName, totalExpositions,recordExposition,numPhotos WITH RESUME;
    END FOREACH;
END PROCEDURE;