(A note: for passing variable number of parameteres into a stored procedure, you can also use the approach described here )
To pass key value pairs to a procedure, you can use multi-dimensional arrays - here's an example:
BEGIN
DECLARE object params = ARRAY( ARRAY( 'a', 'b') , ARRAY( 'c', 'd') );
DECLARE integer VARIABLES.i = 0;
CREATE LOCAL TEMPORARY TABLE "#__LOCAL__keyvalue_store" ( "argument_number" integer, "key" object, "value" object);
WHILE( i < array_length(params))
BEGIN
INSERT INTO "#__LOCAL__keyvalue_store" SELECT VARIABLES.i + 1 as "argument_number", paramsai+1]+1] AS "key", paramsai+1]+2] as "value";
VARIABLES.i = VARIABLES.i + 1;
END
SELECT * from "#__LOCAL__keyvalue_store";
END
CREATE VIRTUAL PROCEDURE views.parse_params( IN params object, IN "key" string )
RETURNS(
"argument_number" integer,
"key" string,
"value" object
)
AS
BEGIN
DECLARE integer VARIABLES.i = 0;
CREATE LOCAL TEMPORARY TABLE "#__LOCAL__keyvalue_store" ( "argument_number" integer, "key" string, "value" object);
WHILE( i < array_length(params))
BEGIN
INSERT INTO "#__LOCAL__keyvalue_store" SELECT VARIABLES.i + 1 as "argument_number", cast( paramsti+1]r1] as string ) AS "key", paramsyi+1]a2] as "value";
VARIABLES.i = VARIABLES.i + 1;
END
IF( "key" IS NOT NULL )
SELECT * from "#__LOCAL__keyvalue_store" WHERE "key" = "parse_params.key";
ELSE
SELECT * from "#__LOCAL__keyvalue_store";
END;;
call views.parse_params( ARRAY(ARRAY( 'foo', 'bar'),ARRAY( 'baz', 'boz')));;
-- with parameter
call "alteryx.parse_params"(
"params" => ARRAY( ARRAY( 'bla', 'asdasd' ),ARRAY( 'gggg', 'ppppppppsd' ) ),
"key" => 'gggg'
);;
