I am stuck in an situation, we have a few services calling an stored procedure.
They pass parameters to a stored procedure, all is fine , till they pass coma spearted parameters ,which need to go into where in () clause of stored procedure.
in short my input parameter is 'ROI,USA,UNK' and I am strugling to get : WHERE COUNTRY_CODE IN ('ROI','USA','UNK').
REPLACE PROCEDURE UPDATE_SUBCOUNTRY
(IN P_EVENT_ID VARCHAR(10),
IN P_COUNTRY_CODE VARCHAR(20),
OUT TOTALL_SALES VARCHAR(20),
OUT CC VARCHAR(20))
DECLARE TOTAL_SALES VARCHAR(20);
DECLARE W_COUNTRY_CODE VARCHAR(20);
SET W_COUNTRY_CODE = ''''||OREPLACE(P_COUNTRY_CODE,',',''',''')||'''';
--SET W_COUNTRY_CODE = OREPLACE(P_COUNTRY_CODE,',',''',''');
SELECT SUM(COALESCE(A.DAILY_SALES,A.YEST_SALES,0)) INTO TOTAL_SALES
FROM EVENT_DAILY A
WHERE A.EVENT_ID = P_EVENT_ID
AND A.COUNTRY_CODE IN (W_COUNTRY_CODE);
Appreciate any help on this,Thank you