How to pass an Integer Array to a Stored Procedure in mysql?

[Shifali Rana]

Hi

I want to pass the Stored Procedure "1,2,3" as a Integer Array, is this possible in mysql?

Actually I have to do
select * FROM User WHERE id IN (1,2,3);
I want to pass the Stored Procedure "1,2,3" as a Integer Array

If i use varchar then sometimes the query executes fine But, sometimes it gives no records.


Thanks

[Agador Paloi]

You could parse the string of numbers , such as '10,20,30' into an array of integers with a function you write using
substr and instr functions. I am not sure if that is what you are talking about.


Agad

[Shifali Rana]

Hi

Thanks Agad

Can you please give some example of a procedure that passes an integer array.
What i know is that we have to create a temporay table which contains the value that we have to pass to where clause.


Is it the only way to create an integer array in mysql?

Shifali

[Agador Paloi]

You will need to create a type that is a table of integers ( an array of integers type)

   
create or replace
   TYPE   my_integer_array   as TABLE OF INTEGER;
 

Then use it as a type for your function or procedure

  

create or replace
function read_array( a_array IN   my_integer_array )
  return  varchar2
  AS
  BEGIN
      FOR  i IN  a_array.first..a_array.last  LOOP     
           dbms_output.put_line( ' print one ' || a_array(i)  );
      END LOOP;   
      return 'ok';
  
  END;

  

should work