-- Stored Procedure for Insertion / Update of Vaccination Control Parental details.

-- Wm. G. Urquhart 15th May, 2002

-- For PostgreSQL v7.2.1

-- Application InocuTrack v1.0.3

-- Script Version 1.0



CREATE OR REPLACE FUNCTION apply_parent_details(integer, integer, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar) RETURNS INTEGER as '

DECLARE

    id ALIAS FOR $1 ;

    ptitle ALIAS FOR $2 ;

    pforename ALIAS FOR $3 ;

    psurname ALIAS FOR $4 ;

    addr1 ALIAS FOR $5 ;

    addr2 ALIAS FOR $6 ;

    addr3 ALIAS FOR $7 ;

    town ALIAS FOR $8 ;

    postcode ALIAS FOR $9 ;

    telno1 ALIAS FOR $10 ;

    telno2 ALIAS FOR $11 ;

    email ALIAS FOR $12 ;

    nParent integer := 0 ;

    nReturn integer := 0 ;

    nOID integer := 0 ;

BEGIN

    IF (id = 0) THEN

        BEGIN 

            INSERT INTO "Parents" ("MTitle", "MForename", "MSurname", "Addr1", "Addr2", "Addr3", "Town", "Postcode", "TelNo1", "TelNo2", "eMail") 

            VALUES (ptitle, pforename, psurname, addr1, addr2, addr3, town, postcode, telno1, telno2, email) ;

            GET DIAGNOSTICS nOID = RESULT_OID ;

            SELECT "ID" INTO nReturn FROM "Parents" WHERE "oid" = nOID ;

        END ;

    ELSE

        BEGIN

            UPDATE "Parents" SET "MTitle" = ptitle, "MForename" = pforename, "MSurname" = psurname, "Addr1" = addr1, "Addr2" = addr2, "Addr3" = addr3, "Town" = town, "Postcode" = postcode, "TelNo1" = telno1, "TelNo2" =@telno2, "eMail" = email

            WHERE ("ID" = id) ;

            GET DIAGNOSTICS nReturn = ROW_COUNT ;

        END ;

    END IF ;

    RETURN nReturn ; 

END ;

' LANGUAGE 'plpgsql' WITH (iscachable, isstrict) ;