Need to allow multiple values as a parameter

General

Need to allow multiple values as a parameter

Hi,

I have a problem to pass multiple values as a parameter in a store procedure,

I'm passing parameters like call DB_name.Ex_Exec_Summary('M','Y','NAPA','ALL','ALL','ALL','P,T,R','ALL','06','2012','x1ajaybh')

My store procedure like this

REPLACE PROCEDURE DB_Name.Ex_Exec_Summary

 (

    IN Report_Type   CHAR(3),

    IN Report_Brand  CHAR(1),

    IN Report_Division   VARCHAR(20),

    IN Report_Region     VARCHAR(20),

    IN Report_District   VARCHAR(4),

    IN Report_Supercorp  VARCHAR(5),

    IN Report_Channel    VARCHAR(30),  

    IN Report_Customer   VARCHAR(10),

    IN Report_Month  BYTEINT,

    IN Report_Year   SMALLINT,

    IN USERID   VARCHAR(30) ) DYNAMIC RESULT SETS 1  --Parameter Declerations

    --

    --

 BEGIN

  DECLARE  vReportType    CHAR(1);                   --Report Type, it will be either Daily or Monthly

  DECLARE  vReportType2   CHAR(1);

  DECLARE  vReportType3   CHAR(1);

  DECLARE  vReport_Month  BYTEINT;                 --Month of Year passed from Crystal

  DECLARE  vReport_Year   SMALLINT;                 --Year Passed from Crystal

  DECLARE  vDivision      VARCHAR(20); --Customer DIVISION Passed from Crystal

  DECLARE  vRegion        VARCHAR(20);   --Customer REGION Passed from Crystal

  DECLARE  vDistrict      VARCHAR(4); --Customer DISTRICT Passed from Crystal

  DECLARE  vCustomer      VARCHAR(10);             --Customer Number Passed from Crystal

  DECLARE  vSelect_Month  BYTEINT;                 --Month of Year passed from Crystal

  DECLARE  vSelect_Year   SMALLINT;                 --Year Passed from Crystal

  DECLARE  VSuperName     VARCHAR(30);                 --Supercorp Name

  DECLARE  VMonth_Name    VARCHAR(9);                 --Month Name

  DECLARE  vDATE_KEY      INTEGER;

  DECLARE  vchannel       CHAR(1);

  DECLARE  vSalesType     CHAR(1);

  DECLARE  vSupercorp     VARCHAR(5);

  DECLARE  vCurrency_Rate  DECIMAL(13,9);

  DECLARE  vCurrency_Rate2 DECIMAL(13,9);

  DECLARE  vCurrency_Rate3 DECIMAL(13,9);

  -- 

  --Variable the Return Values are wrote into.

  DECLARE  vSQL VARCHAR(63500);     --Size of the Seq.

  DECLARE result_set CURSOR WITH RETURN ONLY FOR stmt1;

     --

  --Program Logic Begins Here

  --Set variable values

  SET  vReport_Month = Report_Month;           --Set Report Month = to Month passed from Crystal

  SET  vReport_Year  = Report_Year;

  SET  vSalesType    = UPPER(REPORT_BRAND);

  SET  vDivision     = TRIM(Report_Division);     

  SET  vRegion       = TRIM(Report_Region);

  SET  vDistrict     = TRIM(Report_District);    

  SET  vCustomer     = TRIM(Report_Customer);      

  SET  vReportType   = SUBSTR(Report_Type,1,1);

  SET  vReportType2  = COALESCE(NULLIF(SUBSTR(Report_Type,2,1), ''),'*');  -- F  for Finance Dollars which excludes Warranty & Returns

                                                             -- I  for Invoice Dollars only

  SET  vReportType3  = UPPER(COALESCE(NULLIF(SUBSTR(Report_Type,3,1),''),'*'));

  SET  vSupercorp    = UPPER(TRIM(Report_Supercorp)); 

  SET  vSuperName    = TRIM(Report_Supercorp); 

  SET  vchannel      = SUBSTR(report_channel,1,1);

In above code through Report_channel parameter I'm trying to pass 3 values as a parameter ('P,T,R')

I need vchannel value as ('P','T','R')

Can any one please help me in this.

Thanks in advance,

Regards,

Sekhar