btn-mdw-ace-server-local/mwconfig/storeprocedure/merge_rpg_call.db2

/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:52:33
 * @ Usage: CALL MWCONFIG.MERGE_RPG_CALL(ID, CMD, HOST, TIMEOUT)
 */

CREATE OR REPLACE PROCEDURE MERGE_RPG_CALL (
	IN ID VARCHAR(256),
	IN CMD VARCHAR(1024),
	IN HOST VARCHAR(256),
	IN TIMEOUT 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 AS T ' ||
		'USING (VALUES (''' || ID || ''', ''' || CMD || ''', ''' || HOST || ''', ' || TIMEOUT || ', ''' ||
		MODIFIED_BY || ''', ''' || TIMESTAMP || ''')) AS S (ID, CMD, HOST, TIMEOUT, MODIFIED_BY, TIMESTAMP) ' ||
		'ON (T.ID = S.ID) ' ||
		'WHEN MATCHED THEN ' ||
		'    UPDATE SET ' ||
		'        T.CMD = S.CMD, ' ||
		'        T.HOST = S.HOST, ' ||
		'        T.TIMEOUT = S.TIMEOUT, ' ||
		'        T.MODIFIED_BY = S.MODIFIED_BY, ' ||
		'        T.TIMESTAMP = S.TIMESTAMP ' ||
		'WHEN NOT MATCHED THEN ' ||
		'    INSERT (ID, CMD, HOST, TIMEOUT, MODIFIED_BY, TIMESTAMP) ' ||
		'    VALUES (S.ID, S.CMD, S.HOST, S.TIMEOUT, S.MODIFIED_BY, S.TIMESTAMP)';

	PREPARE STMT FROM SQL_STMT;
	EXECUTE STMT;
END
@