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.

Solution 1

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

Solution 2

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  

Solution 3

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

And the MS support ref.

Solution 4

Thanks to JSON support in MySQL you now actually have the ability to pass an array to your MySQL stored procedure. Create a JSON_ARRAY and simply pass it as a JSON argument to your stored procedure. Then in procedure, using MySQL's WHILE loop and MySQL's JSON "pathing" , access each of the elements in the JSON_ARRAY and do as you wish. An example here https://gist.githubusercontent.com/jonathanvx/513066eea8cb5919b648b2453db47890/raw/22f33fdf64a2f292688edbc67392ba2ccf8da47c/json.sql

View more solutions

Comments

[Thorarin]

Starting with SQL Server 2008, you can actually use table variables as stored procedure parameters.

[Jason Loki Smith]

This question was specifically asked in a PostgreSQL or MySql environment, and not MSSQL