CODEWITHSUNDEEP
mysql
Chowdary
Chowdary

Reputation: 53

mysql select is not working for IN

BEGIN
DECLARE cnt1 int(11);
DECLARE cnt2 int(11);
DECLARE demandid varchar(255);
set demandid = replace(pintypeid,';',',');

SELECT 
    COUNT(*) into cnt1
FROM
    UK_Generic_Demand_Details
where status =300 and 
policyNumber =pinpno and
demandTypeId in(Select @demandid) ;

  SELECT
     COUNT(*) into cnt2
 FROM
     UK_Generic_Completed_Demand_Details
     where
      status =300 and 
policyNo =pinpno and
demandTypeId in(select @demandid);
        select cnt1+cnt2 into poutcount; 

END

Can some please suggest me in solving this issue. stored proc will be called from application with pintypeid and policynumber as inputs. policynumber will be single but pintypeid will be like a string.

Upvotes: 0

Views: 31

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You are probably looking for find_in_set

BEGIN
DECLARE cnt1 int(11);
DECLARE cnt2 int(11);
DECLARE demandid varchar(255);
set demandid = replace(pintypeid,';',',');

SELECT 
    COUNT(*) into cnt1
FROM
    UK_Generic_Demand_Details
where status =300 and 
policyNumber =pinpno and
find_in_set(demandTypeId, @demandid) > 0;

  SELECT
     COUNT(*) into cnt2
 FROM
     UK_Generic_Completed_Demand_Details
     where
      status =300 and 
policyNo =pinpno and
find_in_set(demandTypeId, @demandid) > 0;
        select cnt1+cnt2 into poutcount; 

END

Upvotes: 1

Related Questions