/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:52:33
 * @ Usage: CALL MWCONFIG.MERGE_RPG_CALL_ARG(RPG_CALL_ID, ID, NAME, TYPE, LENGTH)
 */

CREATE OR REPLACE PROCEDURE MERGE_RPG_CALL_ARG (
	IN RPG_CALL_ID VARCHAR(256),
	IN ID INTEGER,
	IN NAME VARCHAR(256),
	IN TYPE VARCHAR(64),
	IN LENGTH INTEGER,
	IN MODIFIED_BY VARCHAR(64) DEFAULT CURRENT_USER,
	IN TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
LANGUAGE SQL
BEGIN
	DECLARE SQL_STMT VARCHAR(10000);

	SET SQL_STMT = 'MERGE INTO MWCONFIG.RPG_CALL_ARG AS T ' ||
		'USING (VALUES (''' || RPG_CALL_ID || ''', ' || ID || ', ''' || NAME || ''', ''' || TYPE || ''', ' || LENGTH || ')) AS S (RPG_CALL_ID, ID, NAME, TYPE, LENGTH) ' ||
		'ON (T.RPG_CALL_ID = S.RPG_CALL_ID AND T.ID = S.ID) ' ||
		'WHEN MATCHED THEN ' ||
		'    UPDATE SET ' ||
		'        T.NAME = S.NAME, ' ||
		'        T.TYPE = S.TYPE, ' ||
		'        T.LENGTH = S.LENGTH ' ||
		'WHEN NOT MATCHED THEN ' ||
		'    INSERT (RPG_CALL_ID, ID, NAME, TYPE, LENGTH) ' ||
		'    VALUES (S.RPG_CALL_ID, S.ID, S.NAME, S.TYPE, S.LENGTH)';

	PREPARE STMT FROM SQL_STMT;
	EXECUTE STMT;
END
@