Passing an list in the,where in clause of sql - Teradata sp

Database
Enthusiast

Passing an list in the,where in clause of sql - Teradata sp

Hi Experts,

    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').

 

 

CALL UPDATE_SUBCOUNTRY('1','ROI,USA,UNK',TOTAL_SALES,CC);

 

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))

proc1:
BEGIN


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);

;


END proc1;

 

 

 

Appreciate any help on this,Thank you

  • Stored Procedure
1 REPLY
Enthusiast

Re: Passing an list in the,where in clause of sql - Teradata sp

Refer to below post,

 

List of Arguments as I/P to Procedure


Wiki