--#SET TERMINATOR @

CREATE OR REPLACE PROCEDURE MWCONFIG.MERGE_PARAM_MAP (
    IN P_GROUP VARCHAR(256),
    IN P_NAME VARCHAR(256),
    IN P_VALUE VARCHAR(1024),
    IN P_SEQ INT DEFAULT NULL,
    IN P_DESCRIPTION VARCHAR(256) DEFAULT NULL
)
LANGUAGE SQL
BEGIN
    DECLARE SQL_STMT VARCHAR(10000);

    SET SQL_STMT = 
        'MERGE INTO MWCONFIG.PARAM_MAP AS T ' ||
        'USING (VALUES (' ||
            CASE 
                WHEN P_GROUP IS NULL THEN 'NULL' 
                ELSE '''' || REPLACE(P_GROUP, '''', '''''') || '''' 
            END || ', ' ||
            CASE 
                WHEN P_NAME IS NULL THEN 'NULL' 
                ELSE '''' || REPLACE(P_NAME, '''', '''''') || '''' 
            END || ', ' ||
            CASE 
                WHEN P_VALUE IS NULL THEN 'NULL' 
                ELSE '''' || REPLACE(P_VALUE, '''', '''''') || '''' 
            END || ', ' ||
            CASE 
                WHEN P_SEQ IS NULL THEN 'NULL' 
                ELSE CHAR(P_SEQ) 
            END || ', ' ||
            CASE 
                WHEN P_DESCRIPTION IS NULL THEN 'NULL'
                ELSE '''' || REPLACE(P_DESCRIPTION, '''', '''''') || ''''
            END ||
        ')) AS S ("GROUP", "NAME", "VALUE", "SEQ", "DESCRIPTION") ' ||
        'ON (T."GROUP" = S."GROUP" AND T."NAME" = S."NAME") ' ||
        'WHEN MATCHED THEN ' ||
        '  UPDATE SET ' ||
        '    T."VALUE" = S."VALUE", ' ||
        '    T."SEQ" = S."SEQ", ' ||
        '    T."DESCRIPTION" = S."DESCRIPTION", ' ||
        '    T.MODIFIED_BY = CURRENT_USER, ' ||
        '    T."TIMESTAMP" = CURRENT_TIMESTAMP ' ||
        'WHEN NOT MATCHED THEN ' ||
        '  INSERT ("GROUP", "NAME", "VALUE", "SEQ", "DESCRIPTION", MODIFIED_BY, "TIMESTAMP") ' ||
        '  VALUES (S."GROUP", S."NAME", S."VALUE", S."SEQ", S."DESCRIPTION", CURRENT_USER, CURRENT_TIMESTAMP)';

    EXECUTE IMMEDIATE SQL_STMT;
END
@