Dynamic IN filter list using stored procedure

Hi,

I have a stored procedure that needs to execute a series of SQL queries using a dynamic IN list.
Here is an example:

CREATE or replace PROCEDURE procedure_name(a integer, b ARRAY(INT))
RETURNS text AS DECLARE

BEGIN

query = select count(distinct c) from table WHERE a_filter = a and b_filter IN (b);
returnvalue = SCALAR(query);

END //

The problem is that an array datatype cannot be used in the IN filter. Furthermore, I could not find any other suitable datatypes that would allow me to pass any form of “list” to construct the query above.

The only solution I found was to iterate over the array and create a string list, and then concatenate the query as a string and the TO_QUERY method.
Example:

CREATE or replace PROCEDURE procedure_name(a integer, b ARRAY(INT))
RETURNS text AS DECLARE

BEGIN

< create b_list_string from b >

query = TO_QUERY(concat(‘select count(distinct c) from table WHERE a_filter = a and b_filter IN (’,b_list_string,’);’)):
returnvalue = SCALAR(query);

END //

However, this seems very slow and I am concerned that I loose the benefit of compiling the query only once but have it compile each time the TO_QUERY method is used.

Is there something I am missing? Perhaps another way of creating the IN list dynamically?

Thank you!

try using COLLECT function instead of SCALAR. This should resolve the issue you are facing.

As far as I know COLLECT is only for the result collection, and not for the IN filter list.
Is there a way to dynamically create the IN list?

One thing that comes to mind is to insert the contents of the array into a temp table and change the query to use a join instead of an IN, e.g.:

query = select count(distinct c) from table, temp WHERE a_filter = a and b_filter = temp.value

If the array is not too big, that should work okay. You can create the temp table at the top of the SP and drop it at the bottom.

Here’s an example of the style I was talking about:

create table t(i int);
insert into t values(1), (2), (3), (4);

delimiter //
create or replace procedure p(a array(int)) as
begin
  create temporary table tmp(a int);
  for i in a loop
    insert into tmp values(i);
  end loop;
  echo select * from t, tmp where t.i = tmp.a;
  drop table tmp;
end //
delimiter ;

delimiter //
create or replace procedure d() as
declare
  a array(int) = create_array(2);
begin
  a[0] = 2;
  a[1] = 3;
  call p(a);
end //
delimiter ;

call d();

memsql> call d();
+------+------+
| i    | a    |
+------+------+
|    3 |    3 |
|    2 |    2 |
+------+------+
2 rows in set (0.02 sec)
1 Like