mysql Stored Procedure Dynamic Where Clause

Question

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

bf2020 · Accepted Answer · 2015-03-18 16:09:15Z

2

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

edited Mar 18, 2015 at 16:09

answered Mar 17, 2015 at 22:10

bf2020

7324 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jason Over a year ago

Thanks bf2020, no, if I change it to this formI get no results back at all.. regardless of what I pass in on p_stateid (a number, list of numbers or NULL).

Jason Over a year ago

Sorry, disregard previous comment.. It does work if I use your FULL solution, but the problem with this is that part of the requirements are lost.. I get nothing back if I pass in NULL. I need the query it to ignore this filter entirely if p_stateid is NULL.

Jason Over a year ago

woa.. lucky.. I thought this Q had died.. just noticed your second update after I had deleted.. Thanks bf2020

Collectives™ on Stack Overflow

mysql Stored Procedure Dynamic Where Clause

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related