mysql dynamic sql using IN

Question

I would like to create a sp with the next code:

select *
from document
where cod_emp='AAA'
and cod_situation in ('01','02','03');

But I would like ('01','02','03') to be a parameter, so I thought something like this would work:

-- sp's call

call sp('"01","02","03"');

-- sp's parameter

in param_txt_cod_situation text;

-- sp's code

set @cons1=
'select *
from document
where cod_emp="AAA" ';

if length(param_txt_cod_situation)>0 then
set @param_txt_cod_situation=param_txt_cod_situation;
set @cons2='and cod_situacion in concat("(",?,")") '
else
set @param_txt_cod_situation=true;
set @cons2='and ?';
end if;

set @cons=concat(
@cons1,
@cons2);

prepare cons from @cons;
execute cons using
@param_txt_cod_situation;
deallocate prepare cons;

But it didn't work.

Hope you guys can help me. Thanks very much.

What doesn't work? Does it not work as you have intended or is an error being thrown? — Matt Dodge
– Matt Dodge, Commented Mar 22, 2012 at 22:58
I get an error. I thought it will be simple like using LIKE: and cod_situacion LIKE concat("%",?,"%") — Outhrics
– Outhrics, Commented Mar 23, 2012 at 15:08
The problem is CONCAT is returning a string, but in requires a list. You should just concatenate your parameters in the procedure, not in the actual query — Matt Dodge
– Matt Dodge, Commented Mar 23, 2012 at 16:16

Matt Dodge · Accepted Answer · 2012-03-23 16:19:28Z

1

The concat function returns a string in whatever query it is in. You do not want to see if something is in a string but rather a list.

Replace

set @cons2='and cod_situacion in concat("(",?,")") '

with

set @cons2=concat('and cod_situacion in (',?,') ')

and see if that works

answered Mar 23, 2012 at 16:19

Matt Dodge

11.2k7 gold badges42 silver badges58 bronze badges

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

1 Comment

Outhrics Over a year ago

It worked! thanks very much for your help. But instead of using "?" i used the parameter directly.

Collectives™ on Stack Overflow

mysql dynamic sql using IN

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related