How can I pass an "array" of values to my stored procedure?

Question

I want to be able to pass an "array" of values to my stored procedure, instead of calling "Add value" procedure serially.

Can anyone suggest a way to do it? am I missing something here?

Edit: I will be using PostgreSQL / MySQL, I haven't decided yet.

Answer 1

You didn't indicate, but if you are referring to SQL server, here's one way.

And the MS support ref.

Answer 2

If you use Oracle, see here: http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause#400295

Answer 3

If you plan to use MySQL 5.1, it is not possible to pass in an array.
See the MySQL 5.1 faq
If you plan to use PostgreSQL, it is possible look here

Answer 4

I don't know about passing an actual array into those engines (I work with sqlserver) but here's an idea for passing a delimited string and parsing it in your sproc with this function.

CREATE FUNCTION [dbo].[Split] ( @ItemList NVARCHAR(4000), @delimiter CHAR(1) ) RETURNS @IDTable TABLE (Item VARCHAR(50))
AS

BEGIN
DECLARE @tempItemList NVARCHAR(4000) SET @tempItemList = @ItemList

DECLARE @i INT    
DECLARE @Item NVARCHAR(4000)

SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)

WHILE (LEN(@tempItemList) > 0)
BEGIN
 IF @i = 0
  SET @Item = @tempItemList
 ELSE
  SET @Item = LEFT(@tempItemList, @i - 1)
 INSERT INTO @IDTable(Item) VALUES(@Item)
 IF @i = 0
  SET @tempItemList = ''
 ELSE
  SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
 SET @i = CHARINDEX(@delimiter, @tempItemList)
END 
RETURN

END

Answer 5

Check this link it should help you

http://www.codeproject.com/KB/database/Array_as_Parameter.aspx

Answer 6

For PostgreSQL, you could do something like this:

CREATE OR REPLACE FUNCTION fnExplode(in_array anyarray) RETURNS SETOF ANYELEMENT AS
$$
    SELECT ($1)[s] FROM generate_series(1,array_upper($1, 1)) AS s;
$$
LANGUAGE SQL IMMUTABLE;

Then, you could pass a delimited string to your stored procedure.

Say, param1 was an input param containing '1|2|3|4|5'

The statement: SELECT CAST(fnExplode(string_to_array(param1, '|')) AS INTEGER);

results in a result set that can be joined or inserted.

Likewise, for MySQL, you could do something like this:

DELIMITER $$ CREATE PROCEDURE spTest_Array ( v_id_arr TEXT ) BEGIN DECLARE v_cur_position INT; DECLARE v_remainder TEXT; DECLARE v_cur_string VARCHAR(255); CREATE TEMPORARY TABLE tmp_test ( id INT ) ENGINE=MEMORY;

SET v_remainder = v_id_arr; 
SET v_cur_position = 1;

WHILE CHAR_LENGTH(v_remainder) > 0 AND v_cur_position > 0 DO 
    SET v_cur_position = INSTR(v_remainder, '|'); 
    IF v_cur_position = 0 THEN 
        SET v_cur_string = v_remainder; 
    ELSE 
        SET v_cur_string = LEFT(v_remainder, v_cur_position - 1); 
    END IF; 

    IF TRIM(v_cur_string) != '' THEN 
        INSERT INTO tmp_test
            (id)
        VALUES 
            (v_cur_string);                 
    END IF; 

    SET v_remainder = SUBSTRING(v_remainder, v_cur_position + 1); 
END WHILE; 

SELECT 
    id
FROM 
tmp_test;

DROP TEMPORARY TABLE tmp_test;

END $$

Then simply CALL spTest_Array('1|2|3|4|5') should produce the same result set as the above PostgreSQL query.

Answer 7

As Chris pointed, in PostgreSQL it's no problem - any base type (like int, text) has it's own array subtype, and you can also create custom types including composite ones. For example:

CREATE TYPE test as (
    n int4,
    m int4
);

Now you can easily create array of test:

select ARRAY[
    row(1,2)::test,
    row(3,4)::test,
    row(5,6)::test
];

You can write a function that will multiply n*m for each item in array, and return sum of products:

CREATE OR REPLACE FUNCTION test_test(IN work_array test[]) RETURNS INT4 as $$
DECLARE
    i      INT4;
    result INT4 := 0;
BEGIN
    FOR i IN SELECT generate_subscripts( work_array, 1 ) LOOP
        result := result + work_array[i].n * work_array[i].m;
    END LOOP;
    RETURN result;
END;
$$ language plpgsql;

and run it:

# SELECT test_test(
    ARRAY[
        row(1, 2)::test,
        row(3,4)::test,
        row(5,6)::test
    ]
);
 test_test
-----------
        44
(1 row)

Answer 8

so i think their is no option in mysql to take array parameter for java