btnmw-server-local/mwconfig/storeprocedure/merge_adaptor.db2

/**
 * @ Author: Marshall Telaumbanua
 * @ Create Time: 2023-06-07 19:08:47
 * @ Usage: CALL MWCONFIG.MERGE_ADAPTOR(ID, DESCRIPTION, ASYNCHRONOUS)
 */

CREATE OR REPLACE PROCEDURE MERGE_ADAPTOR (
	IN ID VARCHAR(256),
	IN DESCRIPTION VARCHAR(2000),
	IN ASYNCHRONOUS SMALLINT
)
BEGIN
	DECLARE SQL_STMT VARCHAR(4000);

	SET SQL_STMT = 	'MERGE INTO MWCONFIG.ADAPTOR AS T ' ||
		'USING (VALUES (''' || ID || ''', ' || 
		CASE WHEN DESCRIPTION IS NOT NULL THEN '''' || DESCRIPTION || '''' ELSE 'NULL' END || ', ' || 
		ASYNCHRONOUS || ', ' ||
		'CURRENT USER, CURRENT TIMESTAMP)) ' ||
		'AS S (ID, DESCRIPTION, ASYNCHRONOUS, MODIFIED_BY, TIMESTAMP) ' ||
		'ON (T.ID = S.ID) ' ||
		'WHEN MATCHED THEN ' ||
		'UPDATE SET T.DESCRIPTION = ' || 
		CASE WHEN DESCRIPTION IS NOT NULL THEN 'S.DESCRIPTION' ELSE 'NULL' END || ', T.ASYNCHRONOUS = S.ASYNCHRONOUS, ' ||
		'T.MODIFIED_BY = CURRENT USER, T.TIMESTAMP = CURRENT TIMESTAMP ' ||
		'WHEN NOT MATCHED THEN ' ||
		'INSERT (ID, DESCRIPTION, ASYNCHRONOUS, MODIFIED_BY, TIMESTAMP) ' ||
		'VALUES (S.ID, ' || 
		CASE WHEN DESCRIPTION IS NOT NULL THEN 'S.DESCRIPTION' ELSE 'NULL' END || ', S.ASYNCHRONOUS, CURRENT USER, CURRENT TIMESTAMP)';


	PREPARE STMT FROM SQL_STMT;
	EXECUTE STMT;
END
@