CODEWITHSUNDEEP
mysqlstored-procedures
Jason
Jason

Reputation: 1977

mysql Stored Procedure Dynamic Where Clause

I have the following query in a stored procedure:

SELECT * 
FROM tempProfile
WHERE 1 = 1
and case    when l_primarymailingaddress = 'address1' then find_in_set(add1_stateid,p_stateid) or p_stateid is NULL
            when l_primarymailingaddress = 'address2' then find_in_set(add2_stateid,p_stateid) or p_stateid is NULL end;

I am passing a list of IDs in on p_stateid.

The first case finds matches, but the second case doesn't.

So if, for example, I pass in a 7, I do get back all results that have 7 in add1_stateid, the query is not returning results that have 7 in add2_stateid.

Comparing against every example and description I can find this should work.

Is anyone able to see what I am doing wrong here?

Many Thanks

Upvotes: 0

Views: 604

Answers (1)

bf2020
bf2020

Reputation: 732

Ok - updated my answer to address where you specifically explained why you were using in_set and P_stateid. Does breaking the Case statement into two and using an OR to compare each of them work?

SELECT * 
FROM tempProfile
WHERE 
case when l_primarymailingaddress = 'address1' AND find_in_set(add1_stateid,p_stateid) THEN 1 ELSE 0 END
OR
case when l_primarymailingaddress = 'address2' AND find_in_set(add2_stateid,p_stateid) THEN 1 ELSE 0 END
OR
p_stateid is NULL
;

2nd update - added another OR to return records when p_stateid is NULL

Upvotes: 2

Related Questions