Dynamic IN filter list using stored procedure

Off-Topic
1

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!

2

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

3

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?

4

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.

5

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