Topic
  • No replies
ErikHeylbroeck
ErikHeylbroeck
1 Post

Pinned topic insert array for parameter stored procedure

‏2014-02-08T22:14:48Z |

I have created a stored procedure with some parameters (variables)

the parameters can contain different values, therefore I need to implement arrays;

Could someone help me on the right way?

I have created following but I am still receiving an error in running the script; could anyone check? The select should return all records with the values identified in the parameter before running the procedure.

Thanks,

 

step 1 : create array type:

create type table_name_array as varchar(10) array[100]

step 2 : create stored procedure:

CREATE OR REPLACE PROCEDUREmyproc

 (

p_table_name IN table_name_array ,

 

)

AS

BEGIN

SET p_table_name = Array['T001', 'T002', 'T003'];

 

cursor c_my_table

is

select domain,

group,

table_name,

from mytable

where

 table_name IN (case when p_table_name is null then table_name else p_table_name end)

;